<?php
  function get_vat_tu_autocomplete($string) {
  $query = db_select('dt_vat_tu', 'vt')->extend('PagerDefault');
  $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu'));
  $query->condition('vt.hien_thi', 0);
  $query->condition(
    db_or()->
      condition('vt.ten_vat_tu', '%' . db_like($string) . '%', 'LIKE')->
      condition('vt.ma_vat_tu', $string, '=')->
      condition('vt.ma_vat_tu', 'VT_' . $string, '=')
  );
  $result = $query->limit(20)->execute();
  $data = array();
  while ($rs = $result->fetchObject()) {
    $data[$rs->ten_vat_tu . ' [' . $rs->ma_vat_tu . ']'] = $rs->ten_vat_tu . ' [' . $rs->ma_vat_tu . ']';
  }
  return $data;
}

  function get_de_c_autocomplete($string) {
    $query = db_select('dt_de_c', 'dc')->extend('PagerDefault');
    $query->fields('dc', array('ma_de_c', 'ten_de_c'));
    $query->condition(
      db_or()->
        condition('dc.ten_de_c', '%' . db_like($string) . '%', 'LIKE')->
        condition('dc.ma_de_c', $string, '=')->
        condition('dc.ma_de_c', 'DC_' . $string, '=')
    );
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ten_de_c . ' [' . $rs->ma_de_c . ']'] = $rs->ten_de_c . ' [' . $rs->ma_de_c . ']';
    }
    return $data;
  }

  function validate_ma_vat_tu($ma_vat_tu_arr, $so_luong_arr, $type) {
    $data['validate'] = true;
    if (!empty($ma_vat_tu_arr)) {
      $query = db_select('dt_vat_tu', 'vt');
      $query->fields('vt', array('ma_vat_tu', 'so_luong', 'so_luong_hong'));
      $query->condition('vt.ma_vat_tu', $ma_vat_tu_arr, 'in');
      $result = $query->execute();
      $validate_arr = array();
      $vat_tu_arr = array();
      while ($rs = $result->fetchObject()) {
        $validate_arr[] = $rs->ma_vat_tu;
        $vat_tu_arr[$rs->ma_vat_tu] = $rs;
      }
      foreach ($ma_vat_tu_arr as $key => $val) {
        if (!in_array($val, $validate_arr)) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['ten_vat_tu'] = true;
          $data['message'] = 'Vật tư không tồn tại';
          break;
        }
        if ($type == 'xuat vat tu' && $vat_tu_arr[$val]->so_luong < $so_luong_arr[$key]) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['so_luong'] = true;
          $data['message'] = 'Số lượng trong kho không đủ';
          break;
        }
        if ($type == 'hoi vat tu' && $vat_tu_arr[$val]->so_luong_hong < $so_luong_arr[$key]) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['so_luong'] = true;
          $data['message'] = 'Số lượng trong kho không đủ';
          break;
        }
        if ($type == 'xuat to kt' && $vat_tu_arr[$val]->so_luong < $so_luong_arr[$key]) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['so_luong'] = true;
          $data['message'] = 'Số lượng trong kho không đủ';
          break;
        }
      }
    }
    return $data;
  }

  function validate_ma_de_c($ma_de_c_arr, $so_luong_arr, $so_luong_vung_arr, $so_luong_than_arr, $so_luong_day_arr, $type) {
    $data['validate'] = true;
    if (!empty($ma_de_c_arr)) {
      $query = db_select('dt_de_c', 'dc');
      $query->fields('dc', array('ma_de_c', 'so_luong', 'sl_vung', 'sl_than', 'sl_day'));
      $query->condition('dc.ma_de_c', $ma_de_c_arr, 'in');
      $result = $query->execute();
      $validate_arr = array();
      $de_c_arr = array();
      while ($rs = $result->fetchObject()) {
        $validate_arr[] = $rs->ma_de_c;
        $de_c_arr[$rs->ma_de_c] = $rs;
      }
      foreach ($ma_de_c_arr as $key => $val) {
        if ($type == 'xuat de c' && $de_c_arr[$val]->so_luong < $so_luong_arr[$key]) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['so_luong'] = true;
          $data['message'] = 'Số lượng trong kho không đủ';
          break;
        }
        if ($type == 'xuat de c' && $de_c_arr[$val]->sl_vung < $so_luong_vung_arr[$key]) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['so_luong_vung'] = true;
          $data['message'] = 'Số vung trong kho không đủ';
          break;
        }
        if ($type == 'xuat de c' && $de_c_arr[$val]->sl_than < $so_luong_than_arr[$key]) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['so_luong_than'] = true;
          $data['message'] = 'Số thân trong kho không đủ';
          break;
        }
        if ($type == 'xuat de c' && $de_c_arr[$val]->sl_day < $so_luong_day_arr[$key]) {
          $data['validate'] = false;
          $data['key'] = $key;
          $data['so_luong_day'] = true;
          $data['message'] = 'Số đáy trong kho không đủ';
          break;
        }
      }
      return $data;
    }
  }

  function get_vat_tu_theo_ma($ma_vat_tu) {
    $query = db_select('dt_vat_tu', 'vt');
    $query->fields('vt', array('id', 'ma_vat_tu', 'ten_vat_tu', 'don_vi', 'gia_tien', 'ghi_chu','so_luong','so_luong_hong'));
    $query->condition('vt.ma_vat_tu', $ma_vat_tu, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function xoa_vat_tu($ma_vat_tu) {
    db_update('dt_vat_tu')
      ->fields(array(
                    'hien_thi' => 1,
               ))
      ->condition('ma_vat_tu', $ma_vat_tu)
      ->execute();
  }

  function get_de_c_theo_ma($ma_de_c) {
    $query = db_select('dt_de_c', 'd');
    $query->fields('d', array('id', 'ma_de_c', 'ten_de_c', 'gia_tien', 'don_vi', 'so_luong', 'sl_vung', 'sl_than', 'sl_day'));
    $query->condition('d.ma_de_c', $ma_de_c, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function get_danh_sach_vat_tu($header, $filter) {
    $query = db_select('dt_vat_tu', 'vt')->extend('PagerDefault')->extend('TableSort');
    $query->fields('vt', array('id', 'ma_vat_tu', 'ten_vat_tu', 'so_luong', 'so_luong_hong', 'don_vi', 'gia_tien', 'ghi_chu', 'thoi_gian',));
    if ($filter['ma_ref']) {
      $query->condition(
        db_or()->
          condition('vt.ten_vat_tu', '%' . db_like($filter['ma_ref']) . '%', 'LIKE')->
          condition('vt.ma_vat_tu', $filter['ma_ref'], '=')->
          condition('vt.ma_vat_tu', 'VT_' . $filter['ma_ref'], '=')
      );
    }
    $query->condition('vt.hien_thi', 0);
    // $query->orderBy('vt.ten_vat_tu', 'ASC');
    $query->orderByHeader($header);
    $result = $query->limit(40)->execute();
    $data = array();
    $edit_perm = user_access('sua vat tu');
    $admin_perm = user_access('admin xoa vat tu');
    while ($rs = $result->fetchObject()) {
      $output = '<a href="vat_tu/thong_ke_vat_tu/xoa_vat_tu/' . $rs->ma_vat_tu . '" onclick="return check_xoa();">Xóa</a>';
//      $style_red = '<span style="color: red">'.$rs->so_luong.'</span>';
//      $style_blue = '<span style="color: blue">'.$rs->so_luong.'</span>';
//      $style_yellow = '<span style="color: orange">'.$rs->so_luong.'</span>';
      $data[] = array(
        'ma_vat_tu' => $rs->ma_vat_tu,
        'ten_vat_tu' => $rs->ten_vat_tu,
        //'so_luong' => ($rs->so_luong > 20) ? (($rs->so_luong > 100) ? $style_blue : $style_yellow) : $style_red,
        'so_luong' => $rs->so_luong,
        'so_luong_hong' => $rs->so_luong_hong,
        'don_vi' => $rs->don_vi,
        'gia_tien' => number_format($rs->gia_tien),
        'thanh_tien' => number_format($rs->so_luong * $rs->gia_tien),
        ($admin_perm ? ($output . ' | ')
          : '') .
        ($edit_perm ? l('Sửa', 'vat_tu/thong_ke_vat_tu/sua_vat_tu/' . $rs->ma_vat_tu)
          : '') . dt_view_ghi_chu('vt_' . $rs->id, 'Ghi chú ' . $rs->ten_vat_tu, $rs->ghi_chu)
      );
    }
    return $data;
  }

  function get_ds_loai_de_c() {
    $query = db_select('dt_de_c', 'd')->extend('PagerDefault')->extend('TableSort');
    $query->fields('d', array('id', 'ma_de_c', 'ten_de_c', 'gia_tien', 'so_luong', 'don_vi', 'sl_vung', 'sl_than', 'sl_day'));
    // $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $data = array();
    $edit_perm = user_access('sua loai de c');
    $data[] = array(
      'data' => array(
        array('data' => 'Mã đề C', 'rowspan' => 2),
        array('data' => 'Tên đề C', 'rowspan' => 2),
        array('data' => 'Giá tiền', 'rowspan' => 2),
        array('data' => 'Số lượng tồn', 'colspan' => 3, 'class' => array('text-center')),
        array('data' => 'Đơn vị', 'rowspan' => 2),
        array('data' => 'Thao tác', 'rowspan' => 2),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => 'Vung'),
        array('data' => 'Thân'),
        array('data' => 'Đáy'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    while ($rs = $result->fetchObject()) {
      $data[] = array(
/*        'ma_de_c' => $rs->ma_de_c,
        'ten_de_c' => $rs->ten_de_c,
        'gia_tien' => number_format($rs->gia_tien),
        'so_luong' => $rs->so_luong,
        'don_vi' => $rs->don_vi,
        ($edit_perm ? l('Sửa', 'vat_tu/de_c/sua_de_c/' . $rs->ma_de_c) : '')*/
        array('data' => $rs->ma_de_c, 'rowspan' => 2),
        array('data' => $rs->ten_de_c, 'rowspan' => 2),
        array('data' => number_format($rs->gia_tien), 'rowspan' => 2),
        array('data' => $rs->so_luong, 'colspan' => 3, 'class' => array('text-center')),
        array('data' => $rs->don_vi, 'rowspan' => 2),
        array('data' => ($edit_perm ? l('Sửa', 'vat_tu/de_c/sua_de_c/' . $rs->ma_de_c) : ''), 'rowspan' => 2),
      );
      $data[] = array(
        array('data' => $rs->sl_vung),
        array('data' => $rs->sl_than),
        array('data' => $rs->sl_day),
      );
    }
    return $data;
  }

  function get_thong_ke_vat_tu_ngay($start_date, $end_date, $start_month, $end_month, $ma_ref) {
    $query = db_select('dt_vat_tu', 'vt');
    $query->fields('vt', array('id', 'ma_vat_tu', 'ten_vat_tu', 'thoi_gian'));
    $query->condition('vt.hien_thi', 0);

    $query->addExpression('(SELECT ROUND(SUM(n0.so_luong),3) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n0 WHERE vt.ma_vat_tu = n0.ma_vat_tu AND n0.thoi_gian < :n0_start_month)', 'nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x0 WHERE vt.ma_vat_tu = x0.ma_vat_tu AND x0.thoi_gian < :x0_start_month)', 'xuat_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(nh0.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS nh0 WHERE vt.ma_vat_tu = nh0.ma_vat_tu AND nh0.thoi_gian < :nh0_start_month)', 'xuat_tkt_den_thang', array(':nh0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(d.so_luong),3) FROM {dt_vat_tu_du_lieu} AS d WHERE vt.ma_vat_tu = d.ma_vat_tu AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(ntk0.so_luong),3) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk0 WHERE vt.ma_vat_tu = ntk0.ma_vat_tu AND ntk0.thoi_gian < :ntk0_start_month)', 'nhap_tinh_den_thang', array(':ntk0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(n0.so_luong),3) FROM {dt_san_pham_nhap_chi_tiet} AS n0 WHERE vt.ma_vat_tu = n0.ma_thanh_pham AND n0.thoi_gian < :n0_start_month)', 'nhap_sp_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.so_luong),3) FROM {dt_san_pham_nguyen_chi_tiet} AS x0 WHERE vt.ma_vat_tu = x0.ma_thanh_pham AND x0.thoi_gian < :x0_start_month)', 'nhap_nguyen_sp_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(xk0.xuat_kho),3) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk0 ,{dt_ban_hang} as bh0 WHERE xk0.ma_phieu_ban_hang=bh0.ma_phieu_ban_hang and vt.ma_vat_tu = xk0.ma_thanh_pham AND bh0.thoi_gian < :bh0_start_month)', 'xuat_sp_den_thang', array(':bh0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(nlbht0.nhan_lai),3) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht0 WHERE vt.ma_vat_tu = nlbht0.ma_thanh_pham AND nlbht0.thoi_gian < :nlbht0_start_month)', 'nlbht0_sp_den_thang', array(':nlbht0_start_month' => $start_month));

    // $query->addExpression('(SELECT SUM(h0.so_luong) FROM {dt_vat_tu_xuat_hoi_chi_tiet} AS h0 WHERE vt.ma_vat_tu = h0.ma_vat_tu AND h0.thoi_gian < :h0_start_month)', 'hoi_den_thang', array(':h0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.so_luong),3) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n1 WHERE vt.ma_vat_tu = n1.ma_vat_tu AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x1 WHERE vt.ma_vat_tu = x1.ma_vat_tu AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(h1.so_luong),3) FROM {dt_vat_tu_xuat_hoi_chi_tiet} AS h1 WHERE vt.ma_vat_tu = h1.ma_vat_tu AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'hoi_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(nh1.so_luong),3) FROM {dt_vat_tu_nhap_hong_chi_tiet} AS nh1 WHERE vt.ma_vat_tu = nh1.ma_vat_tu AND nh1.thoi_gian >= :nh1_start_month AND nh1.thoi_gian <= :nh1_end_month)', 'nhap_hong_thang', array(':nh1_start_month' => $start_month, ':nh1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(xk1.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS xk1 WHERE vt.ma_vat_tu = xk1.ma_vat_tu AND xk1.thoi_gian >= :xk1_start_month AND xk1.thoi_gian <= :xk1_end_month)', 'xuat_kt_thang', array(':xk1_start_month' => $start_month, ':xk1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(ntk1.so_luong),3) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk1 WHERE vt.ma_vat_tu = ntk1.ma_vat_tu AND ntk1.thoi_gian >= :ntk1_start_month AND ntk1.thoi_gian <= :ntk1_end_month)', 'nhap_tinh_kho_thang', array(':ntk1_start_month' => $start_month, ':ntk1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n2.so_luong),3) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n2 WHERE vt.ma_vat_tu = n2.ma_vat_tu AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(x2.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x2 WHERE vt.ma_vat_tu = x2.ma_vat_tu AND x2.thoi_gian >= :x2_start_date AND x2.thoi_gian <= :x2_end_date)', 'xuat_ngay', array(':x2_start_date' => $start_date, ':x2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(h2.so_luong),3) FROM {dt_vat_tu_xuat_hoi_chi_tiet} AS h2 WHERE vt.ma_vat_tu = h2.ma_vat_tu AND h2.thoi_gian >= :h2_start_date AND h2.thoi_gian <= :h2_end_date)', 'hoi_ngay', array(':h2_start_date' => $start_date, ':h2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(nh2.so_luong),3) FROM {dt_vat_tu_nhap_hong_chi_tiet} AS nh2 WHERE vt.ma_vat_tu = nh2.ma_vat_tu AND nh2.thoi_gian >= :nh2_start_date AND nh2.thoi_gian <= :nh2_end_date)', 'nhap_hong_ngay', array(':nh2_start_date' => $start_date, ':nh2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(xk3.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS xk3 WHERE vt.ma_vat_tu = xk3.ma_vat_tu AND xk3.thoi_gian >= :xk3_start_date AND xk3.thoi_gian <= :xk3_end_date)', 'xuat_kt_ngay', array(':xk3_start_date' => $start_date, ':xk3_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(nktk2.so_luong),3) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS nktk2 WHERE vt.ma_vat_tu = nktk2.ma_vat_tu AND nktk2.thoi_gian >= :nktk2_start_date AND nktk2.thoi_gian <= :nktk2_end_date)', 'nhap_tinh_kho_ngay', array(':nktk2_start_date' => $start_date, ':nktk2_end_date' => $end_date));
    if ($ma_ref) {
      // $query->condition('vt.ma_vat_tu', $ma_ref);
      $query->condition(
        db_or()->
          condition('vt.ten_vat_tu', '%' . db_like($ma_ref) . '%', 'LIKE')->
          condition('vt.ma_vat_tu', $ma_ref, '=')->
          condition('vt.ma_vat_tu', 'VT_' . $ma_ref, '=')
      );
    }
    $result = $query->orderBy('vt.ten_vat_tu')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/den_ngay/' . $rs->ma_vat_tu),
        array('data' => ($rs->nhap_den_thang + $rs->nhap_tinh_den_thang - $rs->xuat_den_thang - $rs->xuat_tkt_den_thang + $rs->du + $rs->nhap_sp_den_thang + $rs->nhap_nguyen_sp_den_thang - $rs->xuat_sp_den_thang - $rs->nlbht0_sp_den_thang), 'class' => array('text-center')),
        array('data' => ($rs->nhap_thang) ? ($rs->nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_thang) ? ($rs->xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_hong_thang) ? ($rs->nhap_hong_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->hoi_thang) ? ($rs->hoi_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_kt_thang) ? ($rs->xuat_kt_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_tinh_kho_thang) ? ($rs->nhap_tinh_kho_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_ngay) ? ($rs->nhap_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_ngay) ? ($rs->xuat_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_hong_ngay) ? ($rs->nhap_hong_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->hoi_ngay) ? ($rs->hoi_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_kt_ngay) ? ($rs->xuat_kt_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_tinh_kho_ngay) ? ($rs->nhap_tinh_kho_ngay - 0)
          : '', 'class' => array('text-center')
        ),
      );
    }
    return $data;
  }

  function get_thong_ke_vat_tu_thang($start_month, $end_month, $ma_ref) {
    $query = db_select('dt_vat_tu', 'vt');
    $query->fields('vt', array('id', 'ma_vat_tu', 'ten_vat_tu', 'thoi_gian'));
    $query->condition('vt.hien_thi', 0);

    $query->addExpression('(SELECT ROUND(SUM(n0.so_luong),3) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n0 WHERE vt.ma_vat_tu = n0.ma_vat_tu AND n0.thoi_gian < :n0_start_month)', 'nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x0 WHERE vt.ma_vat_tu = x0.ma_vat_tu AND x0.thoi_gian < :x0_start_month)', 'xuat_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(nh0.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS nh0 WHERE vt.ma_vat_tu = nh0.ma_vat_tu AND nh0.thoi_gian < :nh0_start_month)', 'xuat_tkt_den_thang', array(':nh0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(d.so_luong),3) FROM {dt_vat_tu_du_lieu} AS d WHERE vt.ma_vat_tu = d.ma_vat_tu AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(ntk0.so_luong),3) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk0 WHERE vt.ma_vat_tu = ntk0.ma_vat_tu AND ntk0.thoi_gian < :ntk0_start_month)', 'nhap_tinh_den_thang', array(':ntk0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(n0.so_luong),3) FROM {dt_san_pham_nhap_chi_tiet} AS n0 WHERE vt.ma_vat_tu = n0.ma_thanh_pham AND n0.thoi_gian < :n0_start_month)', 'nhap_sp_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.so_luong),3) FROM {dt_san_pham_nguyen_chi_tiet} AS x0 WHERE vt.ma_vat_tu = x0.ma_thanh_pham AND x0.thoi_gian < :x0_start_month)', 'nhap_nguyen_sp_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(xk0.xuat_kho),3) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk0 ,{dt_ban_hang} as bh0 WHERE xk0.ma_phieu_ban_hang=bh0.ma_phieu_ban_hang and vt.ma_vat_tu = xk0.ma_thanh_pham AND bh0.thoi_gian < :bh0_start_month)', 'xuat_sp_den_thang', array(':bh0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(nlbht0.nhan_lai),3) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht0 WHERE vt.ma_vat_tu = nlbht0.ma_thanh_pham AND nlbht0.thoi_gian < :nlbht0_start_month)', 'nlbht0_sp_den_thang', array(':nlbht0_start_month' => $start_month));


    $query->addExpression('(SELECT ROUND(SUM(n1.so_luong),3) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n1 WHERE vt.ma_vat_tu = n1.ma_vat_tu AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x1 WHERE vt.ma_vat_tu = x1.ma_vat_tu AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(h1.so_luong),3) FROM {dt_vat_tu_xuat_hoi_chi_tiet} AS h1 WHERE vt.ma_vat_tu = h1.ma_vat_tu AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'hoi_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(nh1.so_luong),3) FROM {dt_vat_tu_nhap_hong_chi_tiet} AS nh1 WHERE vt.ma_vat_tu = nh1.ma_vat_tu AND nh1.thoi_gian >= :nh1_start_month AND nh1.thoi_gian <= :nh1_end_month)', 'nhap_hong_thang', array(':nh1_start_month' => $start_month, ':nh1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(xkt1.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS xkt1 WHERE vt.ma_vat_tu = xkt1.ma_vat_tu AND xkt1.thoi_gian >= :xkt1_start_month AND xkt1.thoi_gian <= :xkt1_end_month)', 'xuat_kt_thang', array(':xkt1_start_month' => $start_month, ':xkt1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(ntk1.so_luong),3) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk1 WHERE vt.ma_vat_tu = ntk1.ma_vat_tu AND ntk1.thoi_gian >= :ntk1_start_month AND ntk1.thoi_gian <= :ntk1_end_month)', 'nhap_tinh_kho_thang', array(':ntk1_start_month' => $start_month, ':ntk1_end_month' => $end_month));




    $query->addExpression('(SELECT ROUND(SUM(n2.so_luong),3) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n2 WHERE vt.ma_vat_tu = n2.ma_vat_tu AND n2.thoi_gian < :n2_start_month)', 'nhap_cuoi_thang', array(':n2_start_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x2.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x2 WHERE vt.ma_vat_tu = x2.ma_vat_tu AND x2.thoi_gian < :x2_start_month)', 'xuat_cuoi_thang', array(':x2_start_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(nh2.so_luong),3) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS nh2 WHERE vt.ma_vat_tu = nh2.ma_vat_tu AND nh2.thoi_gian < :nh2_start_month)', 'xuat_tkt_cuoi_thang', array(':nh2_start_month' => $end_month));
    
    $query->addExpression('(SELECT ROUND(SUM(ntk2.so_luong),3) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk2 WHERE vt.ma_vat_tu = ntk2.ma_vat_tu AND ntk2.thoi_gian < :ntk2_start_month)', 'nhap_tinh_cuoi_thang', array(':ntk2_start_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n2.so_luong),3) FROM {dt_san_pham_nhap_chi_tiet} AS n2 WHERE vt.ma_vat_tu = n2.ma_thanh_pham AND n2.thoi_gian < :n2_start_month)', 'nhap_sp_cuoi_thang', array(':n2_start_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x2.so_luong),3) FROM {dt_san_pham_nguyen_chi_tiet} AS x2 WHERE vt.ma_vat_tu = x2.ma_thanh_pham AND x2.thoi_gian < :x2_start_month)', 'nhap_nguyen_sp_cuoi_thang', array(':x2_start_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(xk2.xuat_kho),3) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk2 ,{dt_ban_hang} as bh2 WHERE xk2.ma_phieu_ban_hang=bh2.ma_phieu_ban_hang and vt.ma_vat_tu = xk2.ma_thanh_pham AND bh2.thoi_gian < :bh2_start_month)', 'xuat_sp_cuoi_thang', array(':bh2_start_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(nlbht2.nhan_lai),3) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht2 WHERE vt.ma_vat_tu = nlbht2.ma_thanh_pham AND nlbht2.thoi_gian < :nlbht2_start_month)', 'nlbht2_sp_cuoi_thang', array(':nlbht2_start_month' => $end_month));


    if ($ma_ref) {
      $query->condition(
        db_or()->
          condition('vt.ten_vat_tu', '%' . db_like($ma_ref) . '%', 'LIKE')->
          condition('vt.ma_vat_tu', $ma_ref, '=')->
          condition('vt.ma_vat_tu', 'VT_' . $ma_ref, '=')
      );
    }
    $result = $query->orderBy('vt.ten_vat_tu')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/trong_thang/' . $rs->ma_vat_tu),
        array('data' => ($rs->nhap_den_thang + $rs->nhap_tinh_den_thang - $rs->xuat_den_thang - $rs->xuat_tkt_den_thang + $rs->du + $rs->nhap_sp_den_thang + $rs->nhap_nguyen_sp_den_thang - $rs->xuat_sp_den_thang - $rs->nlbht0_sp_den_thang), 'class' => array('text-center')),
        array('data' => ($rs->nhap_thang) ? ($rs->nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_thang) ? ($rs->xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_hong_thang) ? ($rs->nhap_hong_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->hoi_thang) ? ($rs->hoi_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_kt_thang) ? ($rs->xuat_kt_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_tinh_kho_thang) ? ($rs->nhap_tinh_kho_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_cuoi_thang + $rs->nhap_tinh_cuoi_thang - $rs->xuat_cuoi_thang - $rs->xuat_tkt_cuoi_thang + $rs->du + $rs->nhap_sp_cuoi_thang + $rs->nhap_nguyen_sp_cuoi_thang - $rs->xuat_sp_cuoi_thang - $rs->nlbht2_sp_cuoi_thang), 'class' => array('text-center')),
      );
    }
    return $data;
  }

  function get_thong_ke_chi_tiet_vat_tu($vat_tu, $start_time, $end_time) {
    $query_n = db_select('dt_vat_tu_phieu_nhap_chi_tiet', 'n');
    $query_n->fields('n', array('id', 'so_luong', 'thoi_gian'));
    $query_n->addField('n', 'ma_phieu_nhap', 'type');
    $query_n->condition('n.ma_vat_tu', $vat_tu->ma_vat_tu, '=');
    $query_n->condition('n.thoi_gian', $start_time, '>=');
    $query_n->condition('n.thoi_gian', $end_time, '<=');
    $query_x = db_select('dt_vat_tu_phieu_xuat_chi_tiet', 'x');
    $query_x->fields('x', array('id', 'so_luong', 'thoi_gian'));
    $query_x->addField('x', 'ma_phieu_xuat', 'type');
    $query_x->condition('x.ma_vat_tu', $vat_tu->ma_vat_tu, '=');
    $query_x->condition('x.thoi_gian', $start_time, '>=');
    $query_x->condition('x.thoi_gian', $end_time, '<=');
    $query_h = db_select('dt_vat_tu_xuat_hoi_chi_tiet', 'h');
    $query_h->fields('h', array('id', 'so_luong', 'thoi_gian'));
    $query_h->addField('h', 'ma_phieu_xuat_hoi', 'type');
    $query_h->condition('h.ma_vat_tu', $vat_tu->ma_vat_tu, '=');
    $query_h->condition('h.thoi_gian', $start_time, '>=');
    $query_h->condition('h.thoi_gian', $end_time, '<=');
    $query_nh = db_select('dt_vat_tu_nhap_hong_chi_tiet', 'nh');
    $query_nh->fields('nh', array('id', 'so_luong', 'thoi_gian'));
    $query_nh->addField('nh', 'ma_phieu_nhap_hong', 'type');
    $query_nh->condition('nh.ma_vat_tu', $vat_tu->ma_vat_tu, '=');
    $query_nh->condition('nh.thoi_gian', $start_time, '>=');
    $query_nh->condition('nh.thoi_gian', $end_time, '<=');
    $query_xkt = db_select('dt_vat_tu_phieu_xuat_chi_tiet_tkt', 'xkt');
    $query_xkt->fields('xkt', array('id', 'so_luong', 'thoi_gian'));
    $query_xkt->addField('xkt', 'ma_phieu_xuat', 'type');
    $query_xkt->condition('xkt.ma_vat_tu', $vat_tu->ma_vat_tu, '=');
    $query_xkt->condition('xkt.thoi_gian', $start_time, '>=');
    $query_xkt->condition('xkt.thoi_gian', $end_time, '<=');
    $query_ntk = db_select('dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet', 'ntk');
    $query_ntk->fields('ntk', array('id', 'so_luong', 'thoi_gian'));
    $query_ntk->addField('ntk', 'ma_phieu_nhap', 'type');
    $query_ntk->condition('ntk.ma_vat_tu', $vat_tu->ma_vat_tu, '=');
    $query_ntk->condition('ntk.thoi_gian', $start_time, '>=');
    $query_ntk->condition('ntk.thoi_gian', $end_time, '<=');
    $query_n->union($query_x, 'UNION ALL');
    $query_n->union($query_h, 'UNION ALL');
    $query_n->union($query_nh, 'UNION ALL');
    $query_n->union($query_xkt, 'UNION ALL');
    $query_n->union($query_ntk, 'UNION ALL');
    $query = db_select($query_n, 'vt')->extend('PagerDefault');
    $query->fields('vt', array('type', 'so_luong', 'thoi_gian'));
    $query->orderBy('thoi_gian', 'DESC');
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $type = $rs->type;
      if ($type[1] == 'X') {
        if ($type[2] == 'V') {
          if ($type[4] == 'H') {
            $type = $type[4]; // H - PXVTH
          }
          else {
            $type = $type[1]; // X - PXVT
          }
        }
        else {
          $type = $type[2]; // T - PXTKT
        }
      }
      else {
        if ($type[2] == 'H') {
          $type = $type[1]; //N - PNHH
        }
        else {
          //$type = $type[2]; //V -PNVT
          if ($type[4] == 'T') {
            $type = $type[5]; // K - PNVTTK
          }
          else {
            $type = $type[2]; // V - PNVT
          }
        }
      }
      $date = date('H:i:s d/m/Y', $rs->thoi_gian);
      if (!isset($data[$date])) {
        $data[$date] = array(
          'D' => array('data' => $date, 'class' => array('text-center')),
          'V' => array('data' => '', 'class' => array('text-center')),
          'X' => array('data' => '', 'class' => array('text-center')),
          'N' => array('data' => '', 'class' => array('text-center')),
          'H' => array('data' => '', 'class' => array('text-center')),
          'T' => array('data' => '', 'class' => array('text-center')),
          'K' => array('data' => '', 'class' => array('text-center')),
        );
      }
      if (!$data[$date][$type]['data']) {
        $data[$date][$type]['data'] = $rs->so_luong;
      }
      else {
        $data[$date][$type]['data'] += $rs->so_luong;
      }
    }
    return $data;
  }

  function get_danh_sach_phieu_vat_tu($filter) {
    switch ($filter['type']) {
      case 'nhap':
        $query = db_select('dt_vat_tu_phieu_nhap', 'p')->extend('PagerDefault');
        $query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_nha_cung_cap');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian', 'tong_tien'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->addField('ref', 'ten_nha_cung_cap', 'ref');
        if ($filter['ma_ref']) {
          $query->condition('p.ma_nha_cung_cap', $filter['ma_ref']);
        }
        $link = '../vat_tu/phieu_vat_tu/xoa_phieu/';
        break;
      case 'xuat':
        $query = db_select('dt_vat_tu_phieu_xuat', 'p')->extend('PagerDefault');
        $query->leftJoin('dt_to_san_xuat', 'ref', 'ref.ma_to_san_xuat = p.ma_to_sx');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $query->addField('ref', 'ten_to_san_xuat', 'ref');
        if ($filter['ma_ref']) {
          $query->condition('p.ma_to_sx', $filter['ma_ref']);
        }
        $link = '../../vat_tu/phieu_vat_tu/xoa_phieu/';
        break;
      case 'hoi':
        $query = db_select('dt_vat_tu_xuat_hoi', 'p')->extend('PagerDefault');
        //$query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_nha_cung_cap');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian', 'tong_tien'));
        $query->addField('p', 'ma_phieu_xuat_hoi', 'ma_phieu');
        $query->addField('p', 'ten', 'ref');
        if ($filter['ma_ref']) {
          $query->condition('p.ma_nha_cung_cap', $filter['ma_ref']);
        }
        $link = '../../vat_tu/phieu_vat_tu/xoa_phieu/';
        break;
      case 'nhaptinhkho':
        $query = db_select('dt_vat_tu_phieu_nhap_tinh_kho', 'p')->extend('PagerDefault');
        //  $query->leftJoin('dt_to_san_xuat', 'ref', 'ref.ma_to_san_xuat = p.ma_to_sx');
        $query->fields('p', array('id', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->addField('p', 'ma_to_sx', 'ref');
        if ($filter['ma_ref']) {
          $query->condition('p.ma_to_sx', $filter['ma_ref']);
        }
        $link = '../../vat_tu/phieu_vat_tu/xoa_phieu/';
        break;
    }
    if ($filter['type'] != 'xuat' && $filter['trang_thai'] != -1 && $filter['type'] != 'nhaptinhkho') {
      $query->condition('p.trang_thai', $filter['trang_thai']);
    }
    $result = $query->orderBy('p.thoi_gian', 'DESC')->limit(20)->execute();
    $data = array();
    $admin_perm = user_access('admin xoa vat tu');
    $admin_perm_edit = user_access('admin sua vat tu');
    $khop_phieu = (in_array($filter['type'], array('nhap', 'hoi')) && user_access('khop phieu vat tu'));
    $nhap_gia = (in_array($filter['type'], array('nhap', 'hoi')) && user_access('nhap gia vat tu'));
    $quyen = user_access('xem gia vat tu');
    if ($filter['type'] == 'xuat' || $filter['type'] == 'nhaptinhkho') {
      while ($rs = $result->fetchObject()) {
        $output = '<a href="' . $link . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
        $data[] = array(
          array('data' => $rs->ma_phieu, 'class' => array('')),
          array('data' => $rs->ref, 'class' => array('')),
          array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
          ($admin_perm ? ($output . ' | ')
            : '') . ($admin_perm_edit
            ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' | '
            : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
        );
      }
    }
    else {
      if ($quyen) {
        while ($rs = $result->fetchObject()) {
          $output = '<a href="' . $link . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('')),
            array('data' => $rs->ref, 'class' => array('')),
            array('data' => number_format($rs->tong_tien), 'class' => array('')),
            array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
            ($admin_perm ? ($output . ' | ')
              : '') . ($admin_perm_edit
              ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' | '
              : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) .
            (($khop_phieu && $rs->trang_thai == 0)
              ? ' | ' . l('Khớp phiếu', 'vat_tu/phieu_vat_tu/khop_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
              : '') .
            (($nhap_gia && $rs->tong_tien == 0 && $rs->trang_thai == 1)
              ? ' | ' . l('Nhập giá', 'vat_tu/phieu_vat_tu/nhap_gia/' . $filter['type'] . '/' . $rs->ma_phieu) : '')
          );
        }
      }
      else {
        while ($rs = $result->fetchObject()) {
          $output = '<a href="' . $link . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('')),
            array('data' => $rs->ref, 'class' => array('')),
            array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
            ($admin_perm ? ($output . ' | ')
              : '') . ($admin_perm_edit
              ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' | '
              : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) .
            (($khop_phieu && !$rs->trang_thai)
              ? ' | ' . l('Khớp phiếu', 'vat_tu/phieu_vat_tu/khop_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
              : '') .
            (($nhap_gia && $rs->tong_tien == 0)
              ? ' | ' . l('Nhập giá', 'vat_tu/phieu_vat_tu/nhap_gia/' . $filter['type'] . '/' . $rs->ma_phieu) : '')
          );
        }
      }
    }
    return $data;
  }

  function get_danh_sach_phieu_vat_tu_hong_xtkt($filter) {
    switch ($filter['type']) {
      case 'hong':
        $query = db_select('dt_vat_tu_nhap_hong', 'p')->extend('PagerDefault');
        $query->fields('p', array('id', 'ten_don_vi', 'trang_thai', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_nhap_hong', 'ma_phieu');
        break;
      case 'xuattokt':
        $query = db_select('dt_vat_tu_phieu_xuat_tkt', 'p')->extend('PagerDefault');
        $query->fields('p', array('id', 'ten_nguoi_nhan', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        break;
      case 'xuatdec':
        $query = db_select('dt_de_c_phieu_xuat', 'p')->extend('PagerDefault');
        $query->fields('p', array('id', 'ten_khach_hang', 'la_khach_hang', 'tong_tien', 'trang_thai', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        break;
      case 'nhapdec':
        $query = db_select('dt_de_c_phieu_nhap', 'p')->extend('PagerDefault');
        $query->fields('p', array('id', 'ten_khach_hang', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        break;
    }
    if ($filter['type'] == 'xuatdec' && $filter['trang_thai'] != -1) {
      $query->condition('p.trang_thai', $filter['trang_thai']);
    }
    $result = $query->orderBy('p.thoi_gian', 'DESC')->limit(20)->execute();
    $data = array();
    $khop_phieu = user_access('khop phieu vat tu');
    $nhap_gia = (in_array($filter['type'], array('nhap', 'hoi','xuatdec')) && user_access('nhap gia vat tu'));
    $admin_perm = user_access('admin xoa vat tu');
    $admin_perm_edit = user_access('admin sua vat tu');
    if ($filter['type'] == 'hong') {
      while ($rs = $result->fetchObject()) {
        $output = '<a href="../../vat_tu/phieu_vat_tu/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
        $data[] = array(
          array('data' => $rs->ma_phieu, 'class' => array('')),
          array('data' => $rs->ten_don_vi, 'class' => array('')),
//          array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
          array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
          ($admin_perm ? ($output . ' | ')
            : '') . ($admin_perm_edit
            ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' | '
            : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
        );
      }
    }
    else {
      if ($filter['type'] == 'xuatdec') {
        $quyen = user_access('xem gia vat tu');
        if ($quyen) {
          while ($rs = $result->fetchObject()) {
            $output = '<a href="../../vat_tu/phieu_vat_tu/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
            $data[] = array(
              array('data' => $rs->ma_phieu, 'class' => array('')),
              array('data' => $rs->la_khach_hang ? 'Khách lẻ' : 'Nhà cung cấp', 'class' => array('')),
              array('data' => $rs->ten_khach_hang, 'class' => array('')),
              array('data' => number_format($rs->tong_tien), 'class' => array('')),
              array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
              array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
              ($admin_perm ? ($output . ' | ')
                : '') . ($admin_perm_edit
                ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' | '
                : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
              . (($khop_phieu && !$rs->trang_thai)
                ? ' | ' . l('Khớp phiếu', 'vat_tu/phieu_vat_tu/khop_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
                : '').
                (($nhap_gia && $rs->tong_tien == 0 && $rs->trang_thai == 1)
              ? ' | ' . l('Nhập giá', 'vat_tu/phieu_vat_tu/nhap_gia/' . $filter['type'] . '/' . $rs->ma_phieu) : '')
            );
          }
        }
        else {
          while ($rs = $result->fetchObject()) {
            $output = '<a href="../../vat_tu/phieu_vat_tu/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
            $data[] = array(
              array('data' => $rs->ma_phieu, 'class' => array('')),
              array('data' => $rs->la_khach_hang ? 'Khách lẻ' : 'Nhà cung cấp', 'class' => array('')),
              array('data' => $rs->ten_khach_hang, 'class' => array('')),
              array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
              array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
              ($admin_perm ? ($output . ' | ')
                : '') .
              ($admin_perm_edit
                ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' | '
                : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
              . (($khop_phieu && !$rs->trang_thai)
                ? ' | ' . l('Khớp phiếu', 'vat_tu/phieu_vat_tu/khop_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
                : '')
            );
          }
        }
      }
      else
      {
        if ($filter['type'] == 'nhapdec') {
          while ($rs = $result->fetchObject()) {
            $output = '<a href="../../vat_tu/phieu_vat_tu/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
            $data[] = array(
              array('data' => $rs->ma_phieu, 'class' => array('')),
              array('data' => $rs->ten_khach_hang, 'class' => array('')),
              array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
              ($admin_perm ? ($output . ' | ')
                : '') .
              ($admin_perm_edit
                ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' |  '
                : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
            );
          }
        }
        else {
          while ($rs = $result->fetchObject()) {
            $output = '<a href="../../vat_tu/phieu_vat_tu/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu . '" onclick="return check_xoa_phieu();">Xóa</a>';
            $data[] = array(
              array('data' => $rs->ma_phieu, 'class' => array('')),
              array('data' => $rs->ten_nguoi_nhan, 'class' => array('')),
              array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
              ($admin_perm ? ($output . ' | ')
                : '') .
              ($admin_perm_edit
                ? l('Sửa ', 'vat_tu/phieu_vat_tu/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu) . ' | '
                : '') . l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu)
            );
          }
        }
      }
    }
    return $data;
  }

  function xoa_phieu_vat_tu($type, $ma_phieu) {
    switch ($type) {
      case 'nhap':
        $phieu = get_phieu_vat_tu_theo_ma($type, $ma_phieu);
        update_cong_no_ncc('-', $phieu->tong_tien, $phieu->ma_ref);
        db_delete('dt_vat_tu_phieu_nhap')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_vat_tu;
          $so_luong = $vat_tu->so_luong;
          update_so_luong_vat_tu('-', $so_luong, $ma_vt);
        }
        db_delete('dt_vat_tu_phieu_nhap_chi_tiet')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        break;
      case 'xuat':
        db_delete('dt_vat_tu_phieu_xuat')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_vat_tu;
          $so_luong = $vat_tu->so_luong;
          update_so_luong_vat_tu('+', $so_luong, $ma_vt);
        }
        db_delete('dt_vat_tu_phieu_xuat_chi_tiet')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        break;
      case 'hoi':
        $phieu = get_phieu_vat_tu_theo_ma($type, $ma_phieu);
        update_cong_no_ncc('+', $phieu->tong_tien, $phieu->ma_ref);
        db_delete('dt_vat_tu_xuat_hoi')
          ->condition('ma_phieu_xuat_hoi', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_vat_tu;
          $so_luong = $vat_tu->so_luong;
          update_so_luong_vat_tu_hong('+', $so_luong, $ma_vt);
        }
        db_delete('dt_vat_tu_xuat_hoi_chi_tiet')
          ->condition('ma_phieu_xuat_hoi', $ma_phieu)
          ->execute();
        break;
      case 'nhaptinhkho':
        db_delete('dt_vat_tu_phieu_nhap_tinh_kho')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_vat_tu;
          $so_luong = $vat_tu->so_luong;
          update_so_luong_vat_tu('-', $so_luong, $ma_vt);
        }
        db_delete('dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        break;
      case 'hong':
        db_delete('dt_vat_tu_nhap_hong')
          ->condition('ma_phieu_nhap_hong', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_vat_tu;
          $so_luong = $vat_tu->so_luong;
          update_so_luong_vat_tu_hong('-', $so_luong, $ma_vt);
        }
        db_delete('dt_vat_tu_nhap_hong_chi_tiet')
          ->condition('ma_phieu_nhap_hong', $ma_phieu)
          ->execute();
        break;
      case 'xuattokt':
        db_delete('dt_vat_tu_phieu_xuat_tkt')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_vat_tu;
          $so_luong = $vat_tu->so_luong;
          update_so_luong_vat_tu('+', $so_luong, $ma_vt);
        }
        db_delete('dt_vat_tu_phieu_xuat_chi_tiet_tkt')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        break;
      case 'xuatdec':
        $phieu = get_phieu_vat_tu_theo_ma($type, $ma_phieu);
        update_cong_no_ncc('+', $phieu->tong_tien, $phieu->ma_ref);
        db_delete('dt_de_c_phieu_xuat')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_de_c;
          $so_luong = $vat_tu->so_luong;
          $so_luong_vung = $vat_tu->sl_vung;
          $so_luong_than = $vat_tu->sl_than;
          $so_luong_day = $vat_tu->sl_day;
          update_de_c('+', $so_luong, $ma_vt);
          update_de_c_vung_than_day('+', $so_luong_vung, $so_luong_than, $so_luong_day, $ma_vt);
        }
        db_delete('dt_de_c_phieu_xuat_chi_tiet')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        break;
      case 'nhapdec':
        db_delete('dt_de_c_phieu_nhap')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $vat_tu)
        {
          $ma_vt = $vat_tu->ma_de_c;
          $so_luong = $vat_tu->so_luong;
          $so_luong_vung = $vat_tu->sl_vung;
          $so_luong_than = $vat_tu->sl_than;
          $so_luong_day = $vat_tu->sl_day;
          update_de_c('-', $so_luong, $ma_vt);
          update_de_c_vung_than_day('-', $so_luong_vung, $so_luong_than, $so_luong_day, $ma_vt);
        }
        db_delete('dt_de_c_phieu_nhap_chi_tiet')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        break;
      case 'phatsinh':
        $data = get_phieu_vat_tu_theo_ma($type, $ma_phieu);
        update_cong_no_ncc('-', $data->tong_tien, $data->ma_ref);
        db_delete('dt_nha_cung_cap_phat_sinh')
          ->condition('ma_phieu_phat_sinh', $ma_phieu)
          ->execute();
        break;
      case 'bantp':
        db_delete('dt_vat_tu_ban_tp')
          ->condition('ma_phieu_ban_tp', $ma_phieu)
          ->execute();
        break;
    }
  }

  function update_so_luong_vat_tu($sign, $so_luong, $ma_vat_tu) {
    db_update('dt_vat_tu')
      ->expression('so_luong', 'so_luong ' . $sign . ' :so_luong', array(':so_luong' => $so_luong))
      ->condition('ma_vat_tu', $ma_vat_tu)
      ->execute();
    db_update('dt_san_pham')
      ->expression('so_luong', 'so_luong ' . $sign . ' :so_luong', array(':so_luong' => $so_luong))
      ->condition('ma_thanh_pham', $ma_vat_tu)
      ->execute();
  }

  function update_so_luong_vat_tu_hong($sign, $so_luong_hong, $ma_vat_tu) {
    db_update('dt_vat_tu')
      ->expression('so_luong_hong', 'so_luong_hong ' . $sign . ' :so_luong_hong', array(':so_luong_hong' => $so_luong_hong))
      ->condition('ma_vat_tu', $ma_vat_tu)
      ->execute();
    db_update('dt_san_pham')
      ->expression('so_luong_hong', 'so_luong_hong ' . $sign . ' :so_luong_hong', array(':so_luong_hong' => $so_luong_hong))
      ->condition('ma_thanh_pham', $ma_vat_tu)
      ->execute();
  }

  function insert_loai_vat_tu($uid, $ten_vat_tu, $don_vi, $so_luong, $so_luong_hong, $ghi_chu, $gia_tien) {
    $trang_thai = 1;
    $thoi_gian = time();
    $query = db_insert('dt_vat_tu');
    $arr = array(
      'uid' => $uid,
      'trang_thai' => $trang_thai,
      'ma_vat_tu' => '',
      'ten_vat_tu' => $ten_vat_tu,
      'don_vi' => $don_vi,
      'so_luong' => $so_luong,
      'so_luong_hong' => $so_luong_hong,
      'gia_tien' => $gia_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_vat_tu = $id;
    while (strlen($ma_vat_tu) < 3) {
      $ma_vat_tu = '0' . $ma_vat_tu;
    }
    $ma_vat_tu = 'VT_' . $ma_vat_tu;
    db_update('dt_vat_tu')
      ->fields(array(
                    'ma_vat_tu' => $ma_vat_tu,
               ))
      ->condition('id', $id)
      ->execute();
    $query_n = db_insert('dt_vat_tu_du_lieu');
    $arr_n = array(
      'uid' => $uid,
      'ma_vat_tu' => $ma_vat_tu,
      'so_luong' => $so_luong,
      'so_luong_hong' => $so_luong_hong,
      'thoi_gian' => $thoi_gian
    );
    $query_n->fields(array_keys($arr_n));
    $query_n->values($arr_n);
    $query_n->execute();
    return $ma_vat_tu;
  }

  function insert_de_c($uid, $ten_de_c, $so_tien, $don_vi, $ton_kho, $sl_vung, $sl_than, $sl_day) {
    $thoi_gian = time();
    $query = db_insert('dt_de_c');
    $arr = array(
      'uid' => $uid,
      'ma_de_c' => '',
      'ten_de_c' => $ten_de_c,
      'gia_tien' => $so_tien,
      'don_vi' => $don_vi,
      'so_luong' => $ton_kho,
      'sl_vung' => $sl_vung,
      'sl_than' => $sl_than,
      'sl_day' => $sl_day,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_de_c = $id;
    while (strlen($ma_de_c) < 3) {
      $ma_de_c = '0' . $ma_de_c;
    }
    $ma_de_c = 'DC_' . $ma_de_c;
    db_update('dt_de_c')
      ->fields(array(
                    'ma_de_c' => $ma_de_c,
               ))
      ->condition('id', $id)
      ->execute();
    $query_n = db_insert('dt_de_c_du_lieu');
    $arr_n = array(
      'uid' => $uid,
      'ma_de_c' => $ma_de_c,
      'so_luong' => $ton_kho,
      'sl_vung' => $sl_vung,
      'sl_than' => $sl_than,
      'sl_day' => $sl_day,
      'thoi_gian' => $thoi_gian
    );
    $query_n->fields(array_keys($arr_n));
    $query_n->values($arr_n);
    $query_n->execute();
    return $ma_de_c;
  }

  function insert_vat_tu_hoi($uid, $ma_nha_cung_cap, $ten, $ghi_chu, $trang_thai, $thoi_gian,$la_ncc) {
    $tong_tien = 0;
    $query = db_insert('dt_vat_tu_xuat_hoi');
    $arr = array(
      'uid' => $uid,
      'ma_nha_cung_cap' => $ma_nha_cung_cap,
      'ma_phieu_xuat_hoi' => '',
      'trang_thai' => $trang_thai,
      'tong_tien' => $tong_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian,
      'ten' => $ten,
      'la_ncc' =>$la_ncc
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_xuat_hoi = $id;
    while (strlen($ma_phieu_xuat_hoi) < 3) {
      $ma_phieu_xuat_hoi = '0' . $ma_phieu_xuat_hoi;
    }
    $ma_phieu_xuat_hoi = 'PXVTH_' . $ma_phieu_xuat_hoi;
    db_update('dt_vat_tu_xuat_hoi')
      ->fields(array(
                    'ma_phieu_xuat_hoi' => $ma_phieu_xuat_hoi,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_xuat_hoi;
  }

  function insert_vat_tu_hong($uid, $don_vi, $ghi_chu, $thoi_gian) {
    $query = db_insert('dt_vat_tu_nhap_hong');
    $arr = array(
      'uid' => $uid,
      'ten_don_vi' => $don_vi,
      'ma_phieu_nhap_hong' => '',
      'ghi_chu' => $ghi_chu,
      'trang_thai' => 0,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_nhap_hong = $id;
    while (strlen($ma_phieu_nhap_hong) < 3) {
      $ma_phieu_nhap_hong = '0' . $ma_phieu_nhap_hong;
    }
    $ma_phieu_nhap_hong = 'PNHH_' . $ma_phieu_nhap_hong;
    db_update('dt_vat_tu_nhap_hong')
      ->fields(array(
                    'ma_phieu_nhap_hong' => $ma_phieu_nhap_hong,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_nhap_hong;
  }

  function insert_vat_tu_hong_chi_tiet($uid, $ma_phieu_nhap, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $query = db_insert('dt_vat_tu_nhap_hong_chi_tiet');
    $query->fields(array('uid', 'ma_vat_tu', 'ma_phieu_nhap_hong', 'so_luong', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_vat_tu) {
      $arr = array(
        'uid' => $uid,
        'ma_vat_tu' => $ma_vat_tu,
        'ma_phieu_nhap_hong' => $ma_phieu_nhap,
        'so_luong' => $so_luong_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_vat_tu_hong('+', $so_luong_arr[$key], $ma_vat_tu);
    }
    $query->execute();
  }

  function insert_vat_tu_hoi_chi_tiet($uid, $ma_phieu_xuat_hoi, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $gia_tien = 0;
    $query = db_insert('dt_vat_tu_xuat_hoi_chi_tiet');
    $query->fields(array('uid', 'ma_vat_tu', 'ma_phieu_xuat_hoi', 'so_luong', 'gia_tien', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_vat_tu) {
      $arr = array(
        'uid' => $uid,
        'ma_vat_tu' => $ma_vat_tu,
        'ma_phieu_xuat_hoi' => $ma_phieu_xuat_hoi,
        'so_luong' => $so_luong_arr[$key],
        'gia_tien' => $gia_tien,
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_vat_tu_hong('-', $so_luong_arr[$key], $ma_vat_tu);
    }
    $query->execute();
  }

  function insert_vat_tu_phieu_nhap($uid, $ma_nha_cung_cap, $ghi_chu, $thoi_gian) {
    $trang_thai = 0;
    $tong_tien = 0;
    $query = db_insert('dt_vat_tu_phieu_nhap');
    $arr = array(
      'uid' => $uid,
      'ma_nha_cung_cap' => $ma_nha_cung_cap,
      'ma_phieu_nhap' => '',
      'trang_thai' => $trang_thai,
      'tong_tien' => $tong_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_nhap = $id;
    while (strlen($ma_phieu_nhap) < 3) {
      $ma_phieu_nhap = '0' . $ma_phieu_nhap;
    }
    $ma_phieu_nhap = 'PNVT_' . $ma_phieu_nhap;
    db_update('dt_vat_tu_phieu_nhap')
      ->fields(array(
                    'ma_phieu_nhap' => $ma_phieu_nhap,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_nhap;
  }

  function insert_vat_tu_phieu_nhap_chi_tiet($uid, $ma_phieu_nhap, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $gia_tien = 0;
    $query = db_insert('dt_vat_tu_phieu_nhap_chi_tiet');
    $query->fields(array('uid', 'ma_vat_tu', 'ma_phieu_nhap', 'so_luong', 'gia_tien', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_vat_tu) {
      $arr = array(
        'uid' => $uid,
        'ma_vat_tu' => $ma_vat_tu,
        'ma_phieu_nhap' => $ma_phieu_nhap,
        'so_luong' => $so_luong_arr[$key],
        'gia_tien' => $gia_tien,
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_vat_tu('+', $so_luong_arr[$key], $ma_vat_tu);
    }
    $query->execute();
  }

  function insert_vat_tu_phieu_nhap_tinh_kho_chi_tiet($uid, $ma_phieu_nhap, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $query = db_insert('dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet');
    $query->fields(array('uid', 'ma_vat_tu', 'ma_phieu_nhap', 'so_luong', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_vat_tu) {
      $arr = array(
        'uid' => $uid,
        'ma_vat_tu' => $ma_vat_tu,
        'ma_phieu_nhap' => $ma_phieu_nhap,
        'so_luong' => $so_luong_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_vat_tu('+', $so_luong_arr[$key], $ma_vat_tu);
    }
    $query->execute();
  }

  function insert_vat_tu_phieu_xuat($uid, $ma_to_san_xuat, $ghi_chu, $thoi_gian) {
    $trang_thai = 0;
    $query = db_insert('dt_vat_tu_phieu_xuat');
    $arr = array(
      'uid' => $uid,
      'ma_to_sx' => $ma_to_san_xuat,
      'ma_phieu_xuat' => '',
      'trang_thai' => $trang_thai,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_xuat = $id;
    while (strlen($ma_phieu_xuat) < 3) {
      $ma_phieu_xuat = '0' . $ma_phieu_xuat;
    }
    $ma_phieu_xuat = 'PXVT_' . $ma_phieu_xuat;
    db_update('dt_vat_tu_phieu_xuat')
      ->fields(array(
                    'ma_phieu_xuat' => $ma_phieu_xuat,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_xuat;
  }

  function insert_vat_tu_phieu_nhap_tinh_kho($uid, $ma_to_san_xuat, $ghi_chu, $thoi_gian) {
    $query = db_insert('dt_vat_tu_phieu_nhap_tinh_kho');
    $arr = array(
      'uid' => $uid,
      'ma_to_sx' => $ma_to_san_xuat,
      'ma_phieu_nhap' => '',
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_xuat = $id;
    while (strlen($ma_phieu_xuat) < 3) {
      $ma_phieu_xuat = '0' . $ma_phieu_xuat;
    }
    $ma_phieu_xuat = 'PNVTTK_' . $ma_phieu_xuat;
    db_update('dt_vat_tu_phieu_nhap_tinh_kho')
      ->fields(array(
                    'ma_phieu_nhap' => $ma_phieu_xuat,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_xuat;
  }

  function insert_vat_tu_phieu_xuat_chi_tiet($uid, $ma_phieu_xuat, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $query = db_insert('dt_vat_tu_phieu_xuat_chi_tiet');
    $query->fields(array('uid', 'ma_vat_tu', 'ma_phieu_xuat', 'so_luong', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_vat_tu) {
      $arr = array(
        'uid' => $uid,
        'ma_vat_tu' => $ma_vat_tu,
        'ma_phieu_xuat' => $ma_phieu_xuat,
        'so_luong' => $so_luong_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_vat_tu('-', $so_luong_arr[$key], $ma_vat_tu);
    }
    $query->execute();
  }

  function get_phieu_vat_tu_theo_ma($type, $ma_phieu) {
    switch ($type) {
      case 'nhap':
        $query = db_select('dt_vat_tu_phieu_nhap', 'p');
        $query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_nha_cung_cap');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian', 'ghi_chu', 'tong_tien'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->addField('ref', 'ten_nha_cung_cap', 'ref');
        $query->addField('ref', 'ma_nha_cung_cap', 'ma_ref');
        $query->addField('ref', 'tien_no', 'tien_no');
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        break;
      case 'xuat':
        $query = db_select('dt_vat_tu_phieu_xuat', 'p');
        $query->leftJoin('dt_to_san_xuat', 'ref', 'ref.ma_to_san_xuat = p.ma_to_sx');
        $query->fields('p', array('id', 'trang_thai', 'ghi_chu', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $query->addField('ref', 'ten_to_san_xuat', 'ref');
        $query->addField('ref', 'ma_to_san_xuat', 'ma_ref');
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        break;
      case 'hoi':
        $query = db_select('dt_vat_tu_xuat_hoi', 'p');
//        $query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_nha_cung_cap');
//        $query->leftJoin('dt_to_san_xuat', 'ref1', 'ref1.ma_to_san_xuat = p.ma_nha_cung_cap');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian', 'ghi_chu', 'tong_tien', 'ten','la_ncc'));
        $query->addField('p', 'ma_phieu_xuat_hoi', 'ma_phieu');
        $query->addField('p', 'ten', 'ref');
        $query->addField('p', 'ma_nha_cung_cap', 'ma_ref');
//        $query->addField('ref1', 'ten_to_san_xuat', 'ref');
//        $query->addField('ref1', 'ma_to_san_xuat', 'ma_ref');
        //  $query->addField('ref', 'tien_no', 'tien_no');
        $query->condition('p.ma_phieu_xuat_hoi', $ma_phieu);
        break;
      case 'hong':
        $query = db_select('dt_vat_tu_nhap_hong', 'p');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian', 'ghi_chu'));
        $query->addField('p', 'ma_phieu_nhap_hong', 'ma_phieu');
        $query->addField('p', 'ten_don_vi', 'ref');
        $query->condition('p.ma_phieu_nhap_hong', $ma_phieu);
        break;
      case 'xuattokt':
        $query = db_select('dt_vat_tu_phieu_xuat_tkt', 'p');
        $query->fields('p', array('id', 'thoi_gian', 'ghi_chu'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $query->addField('p', 'ten_nguoi_nhan', 'ref');
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        break;
      case 'xuatdec':
        $query = db_select('dt_de_c_phieu_xuat', 'p');
        $query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_khach_hang');
        $query->fields('p', array('id', 'la_khach_hang', 'trang_thai', 'tong_tien', 'thoi_gian', 'ghi_chu'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $query->addField('p', 'ten_khach_hang', 'ref');
        $query->addField('p', 'ma_khach_hang', 'ma_ref');
        $query->addField('ref', 'tien_no', 'tien_no');
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        break;
      case 'nhapdec':
        $query = db_select('dt_de_c_phieu_nhap', 'p');
        $query->fields('p', array('id', 'thoi_gian', 'ghi_chu'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->addField('p', 'ten_khach_hang', 'ref');
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        break;
      case 'nhaptinhkho':
        $query = db_select('dt_vat_tu_phieu_nhap_tinh_kho', 'p');
        // $query->leftJoin('dt_to_san_xuat', 'ref', 'ref.ma_to_san_xuat = p.ma_to_sx');
        $query->fields('p', array('id', 'ghi_chu', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->addField('p', 'ma_to_sx', 'ref');
        // $query->addField('ref', 'ma_to_san_xuat', 'ma_ref');
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        break;
      case 'phatsinh':
        $query = db_select('dt_nha_cung_cap_phat_sinh', 'p');
        $query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_khach_hang');
        $query->fields('p', array('id', 'thoi_gian', 'ghi_chu', 'so_tien'));
        $query->addField('p', 'ma_phieu_phat_sinh', 'ma_phieu');
        $query->addField('ref', 'ten_nha_cung_cap', 'ref');
        $query->addField('ref', 'ma_nha_cung_cap', 'ma_ref');
        $query->addField('p', 'so_tien', 'tong_tien');
        $query->condition('p.ma_phieu_phat_sinh', $ma_phieu);
        break;
      case 'bantp':
        $query = db_select('dt_vat_tu_ban_tp', 'p');
          $query->fields('p', array('id', 'thoi_gian', 'ghi_chu', 'tong_tien'));
        $query->addField('p', 'ma_phieu_ban_tp', 'ma_phieu');
        $query->leftJoin('dt_to_san_xuat', 'ref', 'ref.ma_to_san_xuat = p.ma_to_san_xuat');
        $query->addField('ref', 'ten_to_san_xuat', 'ref');
        $query->addField('ref', 'ma_to_san_xuat', 'ma_ref');
        $query->condition('p.ma_phieu_ban_tp', $ma_phieu);
        break;
    }
    $result = $query->execute();
    $rs = $result->fetchObject();
    return $rs;
  }

  function get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu, $get_table = true) {
    switch ($type) {
      case 'nhap':
        $query = db_select('dt_vat_tu_phieu_nhap_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian', 'gia_tien'));
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = p.ma_vat_tu');
        $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu', 'don_vi'));
        break;
      case 'xuat':
        $query = db_select('dt_vat_tu_phieu_xuat_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian'));
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = p.ma_vat_tu');
        $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu', 'don_vi'));
        break;
      case 'hoi':
        $query = db_select('dt_vat_tu_xuat_hoi_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian', 'gia_tien'));
        $query->condition('p.ma_phieu_xuat_hoi', $ma_phieu);
        $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = p.ma_vat_tu');
        $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu', 'don_vi'));
        break;
      case 'hong':
        $query = db_select('dt_vat_tu_nhap_hong_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian'));
        $query->condition('p.ma_phieu_nhap_hong', $ma_phieu);
        $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = p.ma_vat_tu');
        $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu', 'don_vi'));
        break;
      case 'xuattokt':
        $query = db_select('dt_vat_tu_phieu_xuat_chi_tiet_tkt', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian'));
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = p.ma_vat_tu');
        $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu', 'don_vi'));
        break;
      case 'xuatdec':
        $query = db_select('dt_de_c_phieu_xuat_chi_tiet', 'p');
        $query->fields('p', array('id', 'ma_de_c', 'don_gia', 'so_luong', 'sl_vung', 'sl_than', 'sl_day', 'thanh_tien', 'thoi_gian'));
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        $query->innerJoin('dt_de_c', 'dc', 'dc.ma_de_c = p.ma_de_c');
        $query->fields('dc', array('ma_de_c', 'ten_de_c', 'don_vi'));
        break;
      case 'nhapdec':
        $query = db_select('dt_de_c_phieu_nhap_chi_tiet', 'p');
        $query->fields('p', array('id', 'ma_de_c', 'so_luong', 'sl_vung', 'sl_than', 'sl_day', 'thoi_gian'));
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        $query->innerJoin('dt_de_c', 'dc', 'dc.ma_de_c = p.ma_de_c');
        $query->fields('dc', array('ma_de_c', 'ten_de_c', 'don_vi'));
        break;
      case 'nhaptinhkho':
        $query = db_select('dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian'));
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = p.ma_vat_tu');
        $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu', 'don_vi'));
        break;
    }

    //    $query->innerJoin('dt_vat_tu', 'vt', 'vt.ma_vat_tu = p.ma_vat_tu');
    //    $query->fields('vt', array('ma_vat_tu', 'ten_vat_tu', 'don_vi'));
    $result = $query->execute();
    $data = array();
    if ($get_table) {
      $quyen = user_access('xem gia vat tu');
      if ($type == 'xuatdec') {
        if ($quyen) {
          $data['header'] = array(
            'Loại đề c',
            'Số lượng',
            'Số vung',
            'Số thân',
            'Số đáy',
            'Đơn giá',
            'Đơn vị',
            'Số tiền',
          );
          while ($rs = $result->fetchObject()) {
            $data['rows'][] = array(
              array('data' => $rs->ten_de_c, 'class' => array('')),
              array('data' => $rs->so_luong, 'class' => array('')),
              array('data' => $rs->sl_vung, 'class' => array('')),
              array('data' => $rs->sl_than, 'class' => array('')),
              array('data' => $rs->sl_day, 'class' => array('')),
              array('data' => number_format($rs->don_gia), 'class' => array('')),
              array('data' => $rs->don_vi, 'class' => array('')),
              array('data' => number_format($rs->so_luong * $rs->don_gia), 'class' => array(''))
            );
          }
        }
        else {
          $data['header'] = array(
            'Loại đề c',
            'Số lượng',
            'Số vung',
            'Số thân',
            'Số đáy',
            'Đơn vị'
          );
          while ($rs = $result->fetchObject()) {
            $data['rows'][] = array(
              array('data' => $rs->ten_de_c, 'class' => array('')),
              array('data' => $rs->so_luong, 'class' => array('')),
              array('data' => $rs->sl_vung, 'class' => array('')),
              array('data' => $rs->sl_than, 'class' => array('')),
              array('data' => $rs->sl_day, 'class' => array('')),
              array('data' => $rs->don_vi, 'class' => array(''))
            );
          }
        }
      }
      else
      {
        if ($type == 'nhapdec') {
          $data['header'] = array(
            'Loại đề c',
            'Số lượng',
            'Số vung',
            'Số thân',
            'Số đáy',
            'Đơn vị'
          );
          while ($rs = $result->fetchObject()) {
            $data['rows'][] = array(
              array('data' => $rs->ten_de_c, 'class' => array('')),
              array('data' => $rs->so_luong, 'class' => array('')),
              array('data' => $rs->sl_vung, 'class' => array('')),
              array('data' => $rs->sl_than, 'class' => array('')),
              array('data' => $rs->sl_day, 'class' => array('')),
              array('data' => $rs->don_vi, 'class' => array(''))
            );
          }
        }
        else {
          if ($type == 'nhap' || $type == 'hoi') {
            $quyen = user_access('xem gia vat tu');
            if ($quyen) {
              $data['header'] = array(
                'Mã vật tư',
                'Tên vật tư',
                'Số lượng',
                'Đơn vị',
                'Đơn giá'
              );
              while ($rs = $result->fetchObject()) {
                $data['rows'][] = array(
                  array('data' => $rs->ma_vat_tu, 'class' => array('')),
                  array('data' => $rs->ten_vat_tu, 'class' => array('')),
                  array('data' => $rs->so_luong, 'class' => array('')),
                  array('data' => $rs->don_vi, 'class' => array('')),
                  array('data' => number_format($rs->gia_tien), 'class' => array(''))
                );
              }
            }
            else
            {
              $data['header'] = array(
                'Mã vật tư',
                'Tên vật tư',
                'Số lượng',
                'Đơn vị',
              );
              while ($rs = $result->fetchObject()) {
                $data['rows'][] = array(
                  array('data' => $rs->ma_vat_tu, 'class' => array('')),
                  array('data' => $rs->ten_vat_tu, 'class' => array('')),
                  array('data' => $rs->so_luong, 'class' => array('')),
                  array('data' => $rs->don_vi, 'class' => array(''))
                );
              }
            }
          }
          else {
            $data['header'] = array(
              'Mã vật tư',
              'Tên vật tư',
              'Số lượng',
              'Đơn vị',
            );
            while ($rs = $result->fetchObject()) {
              $data['rows'][] = array(
                array('data' => $rs->ma_vat_tu, 'class' => array('')),
                array('data' => $rs->ten_vat_tu, 'class' => array('')),
                array('data' => $rs->so_luong, 'class' => array('')),
                array('data' => $rs->don_vi, 'class' => array(''))
              );
            }
          }
        }
      }
    }
    else {
      while ($rs = $result->fetchObject()) {
        $data[] = $rs;
      }
    }
    return $data;
  }

  function update_trang_thai_phieu_vat_tu($data, $type, $ma_phieu) {
    switch ($type) {
      case 'nhap':
        db_update('dt_vat_tu_phieu_nhap')
          ->fields(array('trang_thai' => 1, 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        break;
      case 'nhap':
        db_update('dt_vat_tu_phieu_xuat')
          ->fields(array('trang_thai' => 1, 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        break;
      case 'hoi':
        db_update('dt_vat_tu_xuat_hoi')
          ->fields(array('trang_thai' => 1, 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_xuat_hoi', $ma_phieu)
          ->execute();
        break;
      case 'hong':
        db_update('dt_vat_tu_nhap_hong')
          ->fields(array('trang_thai' => 1, 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_nhap_hong', $ma_phieu)
          ->execute();
        break;
      case 'xuatdec':
        db_update('dt_de_c_phieu_xuat')
          ->fields(array('trang_thai' => 1))
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        break;
    }
  }

  function update_gia_tien_phieu_vat_tu($data, $type, $ma_phieu) {
    switch ($type) {
      case 'nhap':
        db_update('dt_vat_tu_phieu_nhap')
          ->fields(array('tong_tien' => $data['tong_tien'], 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        foreach ($data['gia_tien'] as $key => $gia_tien) {
          $gia_tien = str_replace(',', '', $gia_tien);
          db_update('dt_vat_tu_phieu_nhap_chi_tiet')
            ->fields(array('gia_tien' => $gia_tien))
            ->condition('id', $data['id'][$key])
            ->execute();
        }
        break;
      case 'hoi':
        db_update('dt_vat_tu_xuat_hoi')
          ->fields(array('tong_tien' => $data['tong_tien'], 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_xuat_hoi', $ma_phieu)
          ->execute();
        foreach ($data['gia_tien'] as $key => $gia_tien) {
          $gia_tien = str_replace(',', '', $gia_tien);
          db_update('dt_vat_tu_xuat_hoi_chi_tiet')
            ->fields(array('gia_tien' => $gia_tien))
            ->condition('id', $data['id'][$key])
            ->execute();
        }
        break;
      case 'xuatdec':
      db_update('dt_de_c_phieu_xuat')
      ->fields(array(
                    'tong_tien' => $data['tong_tien'], 'ghi_chu' => $data['ghi_chu'],
               ))
      ->condition('ma_phieu_xuat', $ma_phieu)
      ->execute();
        foreach ($data['gia_tien'] as $key => $gia_tien) {
          $gia_tien = str_replace(',', '', $gia_tien);
          db_update('dt_de_c_phieu_xuat_chi_tiet')
            ->fields(array('don_gia' => $gia_tien))
            ->condition('id', $data['id'][$key])
            ->execute();
        }
        break;
    }
  }

  function get_phieu_chi_tiet_by_id($type, $id) {
    switch ($type) {
      case 'nhap':
        $query = db_select('dt_vat_tu_phieu_nhap', 'p');
        $query->innerJoin('dt_vat_tu_phieu_nhap_chi_tiet', 'ref', 'ref.ma_phieu_nhap = p.ma_phieu_nhap');
        $query->fields('p', array('trang_thai'));
        $query->fields('ref', array('id', 'ma_vat_tu', 'so_luong'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->condition('ref.id', $id);
        break;
      case 'xuat':
        $query = db_select('dt_vat_tu_phieu_xuat', 'p');
        $query->innerJoin('dt_vat_tu_phieu_xuat_chi_tiet', 'ref', 'ref.ma_phieu_xuat = p.ma_phieu_xuat');
        $query->fields('p', array('trang_thai'));
        $query->fields('ref', array('id', 'ma_vat_tu', 'so_luong'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $query->condition('ref.id', $id);
        break;
      case 'hoi':
        $query = db_select('dt_vat_tu_xuat_hoi', 'p');
        $query->innerJoin('dt_vat_tu_xuat_hoi_chi_tiet', 'ref', 'ref.ma_phieu_xuat_hoi = p.ma_phieu_xuat_hoi');
        $query->fields('p', array('trang_thai'));
        $query->fields('ref', array('id', 'ma_vat_tu', 'so_luong'));
        $query->addField('p', 'ma_phieu_xuat_hoi', 'ma_phieu');
        $query->condition('ref.id', $id);
        break;
      case 'hong':
        $query = db_select('dt_vat_tu_nhap_hong', 'p');
        $query->innerJoin('dt_vat_tu_nhap_hong_chi_tiet', 'ref', 'ref.ma_phieu_nhap_hong = p.ma_phieu_nhap_hong');
        $query->fields('p', array('trang_thai'));
        $query->fields('ref', array('id', 'ma_vat_tu', 'so_luong'));
        $query->addField('p', 'ma_phieu_nhap_hong', 'ma_phieu');
        $query->condition('ref.id', $id);
        break;
      case 'xuatdec':
        $query = db_select('dt_de_c_phieu_xuat', 'p');
        $query->innerJoin('dt_de_c_phieu_xuat_chi_tiet', 'ref', 'ref.ma_phieu_xuat = p.ma_phieu_xuat');
        $query->fields('p', array('trang_thai', 'ma_khach_hang', 'la_khach_hang', 'tong_tien'));
        $query->fields('ref', array('id', 'so_luong', 'don_gia', 'thanh_tien'));
        $query->addField('ref', 'ma_de_c', 'ma_vat_tu');
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $query->condition('ref.id', $id);
        break;
    }
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function update_phieu_vat_tu_chi_tiet($type, $id, $ma_vat_tu, $so_luong, $don_gia = 0) {
    $time = time();
    switch ($type) {
      case 'nhap':
        db_update('dt_vat_tu_phieu_nhap_chi_tiet')
          ->fields(array('ma_vat_tu' => $ma_vat_tu, 'so_luong' => $so_luong, 'thoi_gian' => $time))
          ->condition('id', $id)
          ->execute();
        break;
      case 'xuat':
        db_update('dt_vat_tu_phieu_xuat_chi_tiet')
          ->fields(array('ma_vat_tu' => $ma_vat_tu, 'so_luong' => $so_luong, 'thoi_gian' => $time))
          ->condition('id', $id)
          ->execute();
        break;
      case 'hoi':
        db_update('dt_vat_tu_xuat_hoi_chi_tiet')
          ->fields(array('ma_vat_tu' => $ma_vat_tu, 'so_luong' => $so_luong, 'thoi_gian' => $time))
          ->condition('id', $id)
          ->execute();
        break;
      case 'hong':
        db_update('dt_vat_tu_nhap_hong_chi_tiet')
          ->fields(array('ma_vat_tu' => $ma_vat_tu, 'so_luong' => $so_luong, 'thoi_gian' => $time))
          ->condition('id', $id)
          ->execute();
        break;
      case 'xuatdec':
        db_update('dt_de_c_phieu_xuat_chi_tiet')
          ->fields(array('ma_de_c' => $ma_vat_tu, 'so_luong' => $so_luong, 'don_gia' => $don_gia, 'thanh_tien' => $so_luong * $don_gia, 'thoi_gian' => $time))
          ->condition('id', $id)
          ->execute();
        break;
    }
  }

  function update_tong_tien_phieu_de_c($tong_tien, $ma_phieu) {
    db_update('dt_de_c_phieu_xuat')
      ->fields(array('tong_tien' => $tong_tien))
      ->condition('ma_phieu_xuat', $ma_phieu)
      ->execute();
  }

  function insert_lich_su_phieu_chi_tiet($type, $ma_chi_tiet, $ma_phieu, $ma_vat_tu, $so_luong) {
    if ($type == 'xuatdec') {
      $query = db_insert('dt_de_c_lich_su_phieu');
      $arr = array(
        'ma_phieu_chi_tiet' => $ma_chi_tiet,
        'ma_phieu' => $ma_phieu,
        'ma_de_c' => $ma_vat_tu,
        'so_luong' => $so_luong,
        'thoi_gian' => time()
      );
      $query->fields(array_keys($arr));
      $query->values($arr);
      $query->execute();
    }
    else {
      $query = db_insert('dt_vat_tu_lich_su_phieu');
      $arr = array(
        'ma_phieu_chi_tiet' => $ma_chi_tiet,
        'ma_phieu' => $ma_phieu,
        'ma_vat_tu' => $ma_vat_tu,
        'so_luong' => $so_luong,
        'type' => $type,
        'thoi_gian' => time()
      );
      $query->fields(array_keys($arr));
      $query->values($arr);
      $query->execute();
    }
  }

  function insert_xuat_vat_tu_to_ky_thuat($uid, $don_vi, $ghi_chu, $thoi_gian) {
    $query = db_insert('dt_vat_tu_phieu_xuat_tkt');
    $arr = array(
      'uid' => $uid,
      'ten_nguoi_nhan' => $don_vi,
      'ma_phieu_xuat' => '',
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_xuat = $id;
    while (strlen($ma_phieu_xuat) < 3) {
      $ma_phieu_xuat = '0' . $ma_phieu_xuat;
    }
    $ma_phieu_xuat = 'PXTKT_' . $ma_phieu_xuat;
    db_update('dt_vat_tu_phieu_xuat_tkt')
      ->fields(array(
                    'ma_phieu_xuat' => $ma_phieu_xuat,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_xuat;
  }

  function update_xuat_vat_tu_to_ky_thuat($ma_phieu, $uid, $don_vi, $ghi_chu, $thoi_gian, $type) {
    db_update('dt_vat_tu_phieu_xuat_tkt')
      ->fields(array(
                    'uid' => $uid,
                    'ten_nguoi_nhan' => $don_vi,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_phieu_xuat', $ma_phieu)
      ->execute();
    update_chi_tiet($type, $ma_phieu);
    db_delete('dt_vat_tu_phieu_xuat_chi_tiet_tkt')
      ->condition('ma_phieu_xuat', $ma_phieu)
      ->execute();
  }

  function update_chi_tiet($type, $ma_phieu_xuat) {
    $vat_tu_arr = get_phieu_vat_tu_chi_tiet_theo_ma($type, $ma_phieu_xuat, false);
    foreach ($vat_tu_arr as $vat_tu)
    {
      $ma_vt = $vat_tu->ma_vat_tu;
      $so_luong = $vat_tu->so_luong;
      db_update('dt_vat_tu')
        ->expression('so_luong', 'so_luong ' . '+' . ' :so_luong', array(':so_luong' => $so_luong))
        ->condition('ma_vat_tu', $ma_vt)
        ->execute();
    }
  }

  function insert_xuat_vat_tu_to_ky_thuat_chi_tiet($uid, $ma_phieu_xuat, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $query = db_insert('dt_vat_tu_phieu_xuat_chi_tiet_tkt');
    $query->fields(array('uid', 'ma_vat_tu', 'ma_phieu_xuat', 'so_luong', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_vat_tu) {
      $arr = array(
        'uid' => $uid,
        'ma_vat_tu' => $ma_vat_tu,
        'ma_phieu_xuat' => $ma_phieu_xuat,
        'so_luong' => $so_luong_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_vat_tu('-', $so_luong_arr[$key], $ma_vat_tu);
    }
    $query->execute();
  }

  function get_gia_de_c() {
    $query = db_select('dt_de_c', 'dc');
    $query->fields('dc', array('id', 'ma_de_c', 'ten_de_c', 'gia_tien'));
    $result = $query->orderBy('dc.ten_de_c')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = $rs;
    }
    return $data;
  }

  function insert_phieu_xuat_de_c($uid, $ma_ncc, $ten_ncc, $la_khach_hang, $thoi_gian, $ghi_chu) {
    $query = db_insert('dt_de_c_phieu_xuat');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_xuat' => '',
      'ma_khach_hang' => $ma_ncc,
      'ten_khach_hang' => $ten_ncc,
      'la_khach_hang' => $la_khach_hang,
      'tong_tien' => '0',
      'trang_thai' => '0',
      'thoi_gian' => $thoi_gian,
      'ghi_chu' => $ghi_chu
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_xuat = $id;
    while (strlen($ma_phieu_xuat) < 3) {
      $ma_phieu_xuat = '0' . $ma_phieu_xuat;
    }
    $ma_phieu_xuat = 'PXDEC_' . $ma_phieu_xuat;
    db_update('dt_de_c_phieu_xuat')
      ->fields(array(
                    'ma_phieu_xuat' => $ma_phieu_xuat,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_xuat;
  }

  function insert_phieu_xuat_de_c_chi_tiet($uid, $ma_phieu_xuat, $ma_de_c_arr, $don_gia_de_c_arr, $so_luong_arr, $so_luong_vung_arr, $so_luong_than_arr, $so_luong_day_arr, $thoi_gian, $ma_ncc) {
    $query = db_insert('dt_de_c_phieu_xuat_chi_tiet');
    $tong_tien = 0;
    $first = false;
    foreach ($ma_de_c_arr as $key => $ma_de_c) {
      $so_luong = str_replace(',', '', $so_luong_arr[$key]);
      $so_luong_vung = str_replace(',', '', $so_luong_vung_arr[$key]);
      $so_luong_than = str_replace(',', '', $so_luong_than_arr[$key]);
      $so_luong_day = str_replace(',', '', $so_luong_day_arr[$key]);
      if ($so_luong_vung == '') {
        $so_luong_vung = 0;
      }
      if ($so_luong_than == '') {
        $so_luong_than = 0;
      }
      if ($so_luong_day == '') {
        $so_luong_day = 0;
      }
     // $tong_tien += $don_gia_de_c_arr[$ma_de_c] * $so_luong;
      $arr = array(
        'uid' => $uid,
        'ma_phieu_xuat' => $ma_phieu_xuat,
        'ma_de_c' => $ma_de_c,
        'so_luong' => $so_luong,
        'sl_vung' => $so_luong_vung,
        'sl_than' => $so_luong_than,
        'sl_day' => $so_luong_day,
        'don_gia' => 0,
        'thanh_tien' => 0,
        'thoi_gian' => $thoi_gian
      );
      if (!$first) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $first = true;
      update_de_c('-', $so_luong, $ma_de_c);
      update_de_c_vung_than_day('-', $so_luong_vung, $so_luong_than, $so_luong_day, $ma_de_c);
    }
    $query->execute();
    /*db_update('dt_de_c_phieu_xuat')
      ->fields(array(
                    'tong_tien' => $tong_tien,
               ))
      ->condition('ma_phieu_xuat', $ma_phieu_xuat)
      ->execute();
    if ($ma_ncc != '') {
      update_cong_no_ncc('-', $tong_tien, $ma_ncc);
    }*/
  }

  function update_cong_no_ncc($sign, $so_tien, $ma_ncc) {
    db_update('dt_nha_cung_cap')
      ->expression('tien_no', 'tien_no ' . $sign . ' :tien_no', array(':tien_no' => $so_tien))
      ->condition('ma_nha_cung_cap', $ma_ncc)
      ->execute();
  }

  function update_loai_vat_tu($uid, $ma_vat_tu, $ten_vat_tu, $don_vi, $ghi_chu, $gia_tien) {
    $thoi_gian = time();
    db_update('dt_vat_tu')
      ->fields(array(
                    'uid' => $uid,
                    'ten_vat_tu' => $ten_vat_tu,
                    'don_vi' => $don_vi,
                    'gia_tien' => $gia_tien,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_vat_tu', $ma_vat_tu)
      ->execute();
    db_update('dt_san_pham')
      ->fields(array(
                    'uid' => $uid,
                    'ten_thanh_pham' => $ten_vat_tu,
                    'don_vi' => $don_vi,
                    // 'idloai' => $loai,
                    // 'gia_tien' => $gia_tien,
                    'thoi_gian' => $thoi_gian,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_thanh_pham', $ma_vat_tu)
      ->execute();
  }

  function update_loai_vat_tu_admin($uid, $ma_vat_tu, $ten_vat_tu, $don_vi, $ghi_chu, $gia_tien, $so_luong, $so_luong_hong) {
    $thoi_gian = time();
    db_update('dt_vat_tu')
      ->fields(array(
                    'uid' => $uid,
                    'ten_vat_tu' => $ten_vat_tu,
                    'don_vi' => $don_vi,
                    'gia_tien' => $gia_tien,
                    'so_luong' => $so_luong,
                    'so_luong_hong' => $so_luong_hong,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_vat_tu', $ma_vat_tu)
      ->execute();
    db_update('dt_san_pham')
      ->fields(array(
                    'uid' => $uid,
                    'ten_thanh_pham' => $ten_vat_tu,
                    'don_vi' => $don_vi,
                    // 'idloai' => $loai,
                    // 'gia_tien' => $gia_tien,
                    'so_luong' => $so_luong,
                    'so_luong_hong' => $so_luong_hong,
                    'thoi_gian' => $thoi_gian,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_thanh_pham', $ma_vat_tu)
      ->execute();
  }

  function update_loai_de_c($uid, $ma_de_c, $ten_de_c, $so_tien, $don_vi) {
    $thoi_gian = time();
    db_update('dt_de_c')
      ->fields(array(
                    'uid' => $uid,
                    'ten_de_c' => $ten_de_c,
                    'gia_tien' => $so_tien,
                    'don_vi' => $don_vi,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_de_c', $ma_de_c)
      ->execute();
  }

  function update_loai_de_c_admin($uid, $ma_de_c, $ten_de_c, $so_tien, $don_vi, $so_luong, $sl_vung, $sl_than, $sl_day) {
    $thoi_gian = time();
    db_update('dt_de_c')
      ->fields(array(
                    'uid' => $uid,
                    'ten_de_c' => $ten_de_c,
                    'gia_tien' => $so_tien,
                    'don_vi' => $don_vi,
                    'so_luong' => $so_luong,
                    'sl_vung' => $sl_vung,
                    'sl_than' => $sl_than,
                    'sl_day' => $sl_day,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_de_c', $ma_de_c)
      ->execute();
  }

  function insert_phieu_nhap_de_c($uid, $ten_ncc, $thoi_gian, $ghi_chu) {
    $query = db_insert('dt_de_c_phieu_nhap');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_nhap' => '',
      'ten_khach_hang' => $ten_ncc,
      'thoi_gian' => $thoi_gian,
      'ghi_chu' => $ghi_chu
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_xuat = $id;
    while (strlen($ma_phieu_xuat) < 3) {
      $ma_phieu_xuat = '0' . $ma_phieu_xuat;
    }
    $ma_phieu_xuat = 'PNDEC_' . $ma_phieu_xuat;
    db_update('dt_de_c_phieu_nhap')
      ->fields(array(
                    'ma_phieu_nhap' => $ma_phieu_xuat,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_xuat;
  }

  function insert_phieu_nhap_de_c_chi_tiet($uid, $ma_phieu_nhap, $ma_de_c_arr, $so_luong_arr, $so_luong_vung_arr, $so_luong_than_arr, $so_luong_day_arr, $thoi_gian) {
    $query = db_insert('dt_de_c_phieu_nhap_chi_tiet');
    $first = false;
    foreach ($ma_de_c_arr as $key => $ma_de_c) {
      $so_luong = str_replace(',', '', $so_luong_arr[$key]);
      $so_luong_vung = str_replace(',', '', $so_luong_vung_arr[$key]);
      $so_luong_than = str_replace(',', '', $so_luong_than_arr[$key]);
      $so_luong_day = str_replace(',', '', $so_luong_day_arr[$key]);
      if ($so_luong_vung == '') {
        $so_luong_vung = 0;
      }
      if ($so_luong_than == '') {
        $so_luong_than = 0;
      }
      if ($so_luong_day == '') {
        $so_luong_day = 0;
      }
      // $tong_tien += $don_gia_de_c_arr[$ma_de_c] * $so_luong;
      $arr = array(
        'uid' => $uid,
        'ma_phieu_nhap' => $ma_phieu_nhap,
        'ma_de_c' => $ma_de_c,
        'so_luong' => $so_luong,
        'sl_vung' => $so_luong_vung,
        'sl_than' => $so_luong_than,
        'sl_day' => $so_luong_day,
        'thoi_gian' => $thoi_gian
      );
      if (!$first) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $first = true;
      update_de_c('+', $so_luong, $ma_de_c);
      update_de_c_vung_than_day('+', $so_luong_vung, $so_luong_than, $so_luong_day, $ma_de_c);
    }
    $query->execute();
  }

  function update_de_c($sign, $so_luong, $ma_de_c) {
    db_update('dt_de_c')
      ->expression('so_luong', 'so_luong ' . $sign . ' :so_luong', array(':so_luong' => $so_luong))
      ->condition('ma_de_c', $ma_de_c)
      ->execute();
  }

  function update_de_c_vung_than_day($sign, $so_luong_vung, $so_luong_than, $so_luong_day, $ma_de_c) {
    db_update('dt_de_c')
      ->expression('sl_vung', 'sl_vung ' . $sign . ' :sl_vung', array(':sl_vung' => $so_luong_vung))
      ->expression('sl_than', 'sl_than ' . $sign . ' :sl_than', array(':sl_than' => $so_luong_than))
      ->expression('sl_day', 'sl_day ' . $sign . ' :sl_day', array(':sl_day' => $so_luong_day))
      ->condition('ma_de_c', $ma_de_c)
      ->execute();
  }

  function get_thong_ke_de_c_ngay($start_date, $end_date, $start_month, $end_month) {
    $query = db_select('dt_de_c', 'dc');
    $query->fields('dc', array('id', 'ma_de_c', 'ten_de_c', 'thoi_gian'));
    $query->addExpression('(SELECT ROUND(SUM(n0.so_luong),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n0 WHERE dc.ma_de_c = n0.ma_de_c AND n0.thoi_gian < :n0_start_month)', 'nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(n0.sl_vung),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n0 WHERE dc.ma_de_c = n0.ma_de_c AND n0.thoi_gian < :n0_start_month)', 'vung_nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(n0.sl_than),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n0 WHERE dc.ma_de_c = n0.ma_de_c AND n0.thoi_gian < :n0_start_month)', 'than_nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(n0.sl_day),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n0 WHERE dc.ma_de_c = n0.ma_de_c AND n0.thoi_gian < :n0_start_month)', 'day_nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.so_luong),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x0 WHERE dc.ma_de_c = x0.ma_de_c AND x0.thoi_gian < :x0_start_month)', 'xuat_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.sl_vung),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x0 WHERE dc.ma_de_c = x0.ma_de_c AND x0.thoi_gian < :x0_start_month)', 'vung_xuat_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.sl_than),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x0 WHERE dc.ma_de_c = x0.ma_de_c AND x0.thoi_gian < :x0_start_month)', 'than_xuat_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(x0.sl_day),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x0 WHERE dc.ma_de_c = x0.ma_de_c AND x0.thoi_gian < :x0_start_month)', 'day_xuat_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(d.so_luong),3) FROM {dt_de_c_du_lieu} AS d WHERE dc.ma_de_c = d.ma_de_c AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(d.sl_vung),3) FROM {dt_de_c_du_lieu} AS d WHERE dc.ma_de_c = d.ma_de_c AND d.thoi_gian < :d_start_month)', 'vung_du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(d.sl_than),3) FROM {dt_de_c_du_lieu} AS d WHERE dc.ma_de_c = d.ma_de_c AND d.thoi_gian < :d_start_month)', 'than_du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(d.sl_day),3) FROM {dt_de_c_du_lieu} AS d WHERE dc.ma_de_c = d.ma_de_c AND d.thoi_gian < :d_start_month)', 'day_du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.so_luong),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.sl_vung),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'vung_nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.sl_than),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'than_nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.sl_day),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'day_nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.so_luong),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.sl_vung),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'vung_xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.sl_than),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'than_xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.sl_day),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'day_xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n2.so_luong),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n2 WHERE dc.ma_de_c = n2.ma_de_c AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(n2.sl_vung),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n2 WHERE dc.ma_de_c = n2.ma_de_c AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'vung_nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(n2.sl_than),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n2 WHERE dc.ma_de_c = n2.ma_de_c AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'than_nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(n2.sl_day),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n2 WHERE dc.ma_de_c = n2.ma_de_c AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'day_nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(x2.so_luong),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x2 WHERE dc.ma_de_c = x2.ma_de_c AND x2.thoi_gian >= :x2_start_date AND x2.thoi_gian <= :x2_end_date)', 'xuat_ngay', array(':x2_start_date' => $start_date, ':x2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(x2.sl_vung),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x2 WHERE dc.ma_de_c = x2.ma_de_c AND x2.thoi_gian >= :x2_start_date AND x2.thoi_gian <= :x2_end_date)', 'vung_xuat_ngay', array(':x2_start_date' => $start_date, ':x2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(x2.sl_than),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x2 WHERE dc.ma_de_c = x2.ma_de_c AND x2.thoi_gian >= :x2_start_date AND x2.thoi_gian <= :x2_end_date)', 'than_xuat_ngay', array(':x2_start_date' => $start_date, ':x2_end_date' => $end_date));
    $query->addExpression('(SELECT ROUND(SUM(x2.sl_day),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x2 WHERE dc.ma_de_c = x2.ma_de_c AND x2.thoi_gian >= :x2_start_date AND x2.thoi_gian <= :x2_end_date)', 'day_xuat_ngay', array(':x2_start_date' => $start_date, ':x2_end_date' => $end_date));
    $result = $query->orderBy('dc.ma_de_c')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        array('data' => l($rs->ten_de_c, 'vat_tu/de_c/chi_tiet_de_c/den_ngay/' . $rs->ma_de_c), 'rowspan' => 2),
        array('data' => ($rs->nhap_den_thang - $rs->xuat_den_thang + $rs->du), 'colspan' => 3, 'class' => array('text-center')),
        array('data' => ($rs->nhap_thang) ? ($rs->nhap_thang - 0)
          : '', 'colspan' => 3, 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_thang) ? ($rs->xuat_thang - 0)
          : '', 'colspan' => 3, 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_ngay) ? ($rs->nhap_ngay - 0)
          : '', 'colspan' => 3, 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_ngay) ? ($rs->xuat_ngay - 0)
          : '', 'colspan' => 3, 'class' => array('text-center')
        ),
      );
      $data[] = array(
        array('data' => ($rs->vung_nhap_den_thang - $rs->vung_xuat_den_thang + $rs->vung_du), 'class' => array('text-center')),
        array('data' => ($rs->than_nhap_den_thang - $rs->than_xuat_den_thang + $rs->than_du), 'class' => array('text-center')),
        array('data' => ($rs->day_nhap_den_thang - $rs->day_xuat_den_thang + $rs->day_du), 'class' => array('text-center')),
        array('data' => ($rs->vung_nhap_thang) ? ($rs->vung_nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->than_nhap_thang) ? ($rs->than_nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->day_nhap_thang) ? ($rs->day_nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->vung_xuat_thang) ? ($rs->vung_xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->than_xuat_thang) ? ($rs->than_xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->day_xuat_thang) ? ($rs->day_xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->vung_nhap_ngay) ? ($rs->vung_nhap_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->than_nhap_ngay) ? ($rs->than_nhap_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->day_nhap_ngay) ? ($rs->day_nhap_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->vung_xuat_ngay) ? ($rs->vung_xuat_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->than_xuat_ngay) ? ($rs->than_xuat_ngay - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->day_xuat_ngay) ? ($rs->day_xuat_ngay - 0)
          : '', 'class' => array('text-center')
        ),
      );
    }
    return $data;
  }

  function get_thong_ke_de_c_thang($start_month, $end_month) {
    $query = db_select('dt_de_c', 'dc');
    $query->fields('dc', array('id', 'ma_de_c', 'ten_de_c', 'thoi_gian'));
    $query->addExpression('(SELECT ROUND(SUM(n1.so_luong),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.sl_vung),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'vung_nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.sl_than),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'than_nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(n1.sl_day),3) FROM {dt_de_c_phieu_nhap_chi_tiet} AS n1 WHERE dc.ma_de_c = n1.ma_de_c AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'day_nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.so_luong),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.sl_vung),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'vung_xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.sl_than),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'than_xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT ROUND(SUM(x1.sl_day),3) FROM {dt_de_c_phieu_xuat_chi_tiet} AS x1 WHERE dc.ma_de_c = x1.ma_de_c AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'day_xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $result = $query->orderBy('dc.ma_de_c')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        //l($rs->ten_de_c, 'vat_tu/de_c/chi_tiet_de_c/trong_thang/' . $rs->ma_de_c),
        array('data' => l($rs->ten_de_c, 'vat_tu/de_c/chi_tiet_de_c/trong_thang/' . $rs->ma_de_c), 'rowspan' => 2, 'class' => array('text-center')
        ),
        array('data' => ($rs->nhap_thang) ? ($rs->nhap_thang - 0)
          : '', 'colspan' => 3, 'class' => array('text-center')
        ),
        array('data' => ($rs->xuat_thang) ? ($rs->xuat_thang - 0)
          : '', 'colspan' => 3, 'class' => array('text-center')
        ),
      );
      $data[] = array(
        //l($rs->ten_de_c, 'vat_tu/de_c/chi_tiet_de_c/trong_thang/' . $rs->ma_de_c),
        array('data' => ($rs->vung_nhap_thang) ? ($rs->vung_nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->than_nhap_thang) ? ($rs->than_nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->day_nhap_thang) ? ($rs->day_nhap_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->vung_xuat_thang) ? ($rs->vung_xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->than_xuat_thang) ? ($rs->than_xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
        array('data' => ($rs->day_xuat_thang) ? ($rs->day_xuat_thang - 0)
          : '', 'class' => array('text-center')
        ),
      );
    }
    return $data;
  }

  function get_thong_ke_chi_tiet_de_c($de_c, $start_time, $end_time) {
    $query_n = db_select('dt_de_c_phieu_nhap_chi_tiet', 'n');
    $query_n->fields('n', array('id', 'thoi_gian', 'so_luong', 'sl_vung', 'sl_than', 'sl_day'));
    $query_n->addField('n', 'ma_phieu_nhap', 'type');
    $query_n->condition('n.ma_de_c', $de_c->ma_de_c, '=');
    $query_n->condition('n.thoi_gian', $start_time, '>=');
    $query_n->condition('n.thoi_gian', $end_time, '<=');
    $query_x = db_select('dt_de_c_phieu_xuat_chi_tiet', 'x');
    $query_x->fields('x', array('id', 'thoi_gian', 'so_luong', 'sl_vung', 'sl_than', 'sl_day'));
    $query_x->addField('x', 'ma_phieu_xuat', 'type');
    $query_x->condition('x.ma_de_c', $de_c->ma_de_c, '=');
    $query_x->condition('x.thoi_gian', $start_time, '>=');
    $query_x->condition('x.thoi_gian', $end_time, '<=');
    $query_n->union($query_x, 'UNION ALL');
    $query = db_select($query_n, 'vt')->extend('PagerDefault');
    $query->fields('vt', array('type', 'so_luong', 'thoi_gian', 'sl_vung', 'sl_than', 'sl_day'));
    $query->orderBy('thoi_gian', 'DESC');
    $result = $query->limit(20)->execute();
    $data = array();
    /*$data[] = array(
      'data' => array(
        array('data' => $de_c->ten_de_c, 'colspan' => 7, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-san-pham-header')
    );*/
    $i = 1;
    while ($rs = $result->fetchObject()) {
      $i++;
      $type = $rs->type;
      $type = $type[1];
      $date = date('H:i:s d/m/Y', $rs->thoi_gian);
      if (!isset($data[$date])) {
        $data[$date] = array(
          'D' => array('data' => $date, 'rowspan' => 2, 'class' => array('text-center')),
          'N' => array('data' => '', 'colspan' => 3, 'class' => array('text-center')),
          'X' => array('data' => '', 'colspan' => 3, 'class' => array('text-center')),
        );
        $data[$i] = array(
          '1' => array('data' => '', 'class' => array('text-center')),
          '2' => array('data' => '', 'class' => array('text-center')),
          '3' => array('data' => '', 'class' => array('text-center')),
          '4' => array('data' => '', 'class' => array('text-center')),
          '5' => array('data' => '', 'class' => array('text-center')),
          '6' => array('data' => '', 'class' => array('text-center')),
        );
      }
      if (!$data[$date][$type]['data']) {
        $data[$date][$type]['data'] = $rs->so_luong;
        if ($type == 'N') {
          $data[$i][1]['data'] = $rs->sl_vung;
          $data[$i][2]['data'] = $rs->sl_than;
          $data[$i][3]['data'] = $rs->sl_day;
        }
        else {
          $data[$i][4]['data'] = $rs->sl_vung;
          $data[$i][5]['data'] = $rs->sl_than;
          $data[$i][6]['data'] = $rs->sl_day;
        }
      }
      else {
        $data[$date][$type]['data'] += $rs->so_luong;
        if ($type == 'N') {
          $data[$date][$i]['data'] = $rs->sl_vung;
          $data[$date][$i]['data'] = $rs->sl_than;
          $data[$date][$i]['data'] = $rs->sl_day;
        }
        else {
          $data[$date][$i]['data'] = $rs->sl_vung;
          $data[$date][$i]['data'] = $rs->sl_than;
          $data[$date][$i]['data'] = $rs->sl_day;
        }
      }
    }
    return $data;
  }

  function get_thong_ke_ban_tp_thang($start_month, $end_month) {
    $query = db_select('dt_vat_tu_ban_tp', 'p');
    $query->innerJoin('dt_to_san_xuat', 'tsx', 'p.ma_to_san_xuat = tsx.ma_to_san_xuat');
    $query->fields('p', array('id', 'ma_phieu_ban_tp', 'tong_tien', 'ghi_chu', 'thoi_gian'));
    $query->fields('tsx', array('ten_to_san_xuat'));
    $query->condition('p.thoi_gian', $start_month, '>=');
    $query->condition('p.thoi_gian', $end_month, '<=');
    $result = $query->orderBy('p.thoi_gian')->execute();
    $data = array();
    $admin_perm = user_access('admin xoa vat tu');
    $admin_perm_edit = user_access('admin sua vat tu');
    $tong_tien = 0;
    while ($rs = $result->fetchObject()) {
      $output = '<a href="../vat_tu/phieu_vat_tu/xoa_phieu/bantp/' . $rs->ma_phieu_ban_tp . '" onclick="return check_xoa_phieu();">Xóa</a>';
      $tong_tien += $rs->tong_tien;
      $data[] = array(
        $rs->ma_phieu_ban_tp,
        $rs->ten_to_san_xuat,
        number_format($rs->tong_tien),
        date('H:i:s d/m/Y', $rs->thoi_gian),
        array(
          'data' => ($admin_perm ? ($output . ' | ')
            : '') .
                    ($admin_perm_edit ? (l('Sửa', 'vat_tu/phieu_vat_tu/sua_phieu/bantp/' . $rs->ma_phieu_ban_tp) . ' | ')
            : '') .
                    l('Chi tiết', 'vat_tu/ban_thanh_pham/chi_tiet_ban_tp/' . $rs->ma_phieu_ban_tp) .

                    dt_view_ghi_chu('pbtp_' . $rs->id, 'Ghi chú phiếu bán thành phẩm ' . $rs->ma_phieu_ban_tp, $rs->ghi_chu),
          'class' => 'thao-tac'
        )
      );
    }
    if (empty($data)) {
      $data[] = array(
        array('data' => 'Không tìm thấy phiếu nào trong tháng', 'colspan' => 5, 'class' => array('text-center'))
      );
    }
    else {
      $data[] = array(
        '',
        array('data' => 'Tổng tiền', 'class' => array('text-bold')),
        array('data' => number_format($tong_tien), 'class' => array('text-bold')),
        '',
        ''
      );
    }
    return $data;
  }

  function insert_phieu_ban_tp($uid, $ma_to_san_xuat, $tong_tien, $ghi_chu, $thoi_gian) {
    $query = db_insert('dt_vat_tu_ban_tp');
    $arr = array(
      'uid' => $uid,
      'ma_to_san_xuat' => $ma_to_san_xuat,
      'ma_phieu_ban_tp' => '',
      'tong_tien' => $tong_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu = $id;
    while (strlen($ma_phieu) < 3) {
      $ma_phieu = '0' . $ma_phieu;
    }
    $ma_phieu = 'PBTP_' . $ma_phieu;
    db_update('dt_vat_tu_ban_tp')
      ->fields(array(
                    'ma_phieu_ban_tp' => $ma_phieu,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu;
  }

  function insert_phieu_ban_tp_chi_tiet($uid, $ma_phieu, $ten_vat_tu_arr, $so_luong_arr, $don_vi_arr, $don_gia_arr, $thoi_gian) {
    $query = db_insert('dt_vat_tu_ban_tp_chi_tiet');
    $query->fields(array('uid', 'ten_vat_tu', 'ma_phieu_ban_tp', 'don_vi', 'so_luong', 'don_gia', 'thoi_gian'));
    foreach ($ten_vat_tu_arr as $key => $ten_vat_tu) {
      $arr = array(
        'uid' => $uid,
        'ma_phieu_ban_tp' => $ma_phieu,
        'ten_vat_tu' => $ten_vat_tu,
        'don_vi' => $don_vi_arr[$key],
        'so_luong' => $so_luong_arr[$key],
        'don_gia' => str_replace(',', '', $don_gia_arr[$key]),
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      //      update_so_luong_vat_tu('-', $so_luong_arr[$key], $ma_vat_tu);
    }
    $query->execute();
  }

  function get_phieu_ban_tp_theo_ma($ma_phieu) {
    $query = db_select('dt_vat_tu_ban_tp', 'p');
    $query->innerJoin('dt_to_san_xuat', 'tsx', 'p.ma_to_san_xuat = tsx.ma_to_san_xuat');
    $query->fields('p', array('id', 'ma_phieu_ban_tp', 'tong_tien', 'ghi_chu', 'thoi_gian'));
    $query->fields('tsx', array('ten_to_san_xuat'));
    $query->condition('p.ma_phieu_ban_tp', $ma_phieu);
    return $query->execute()->fetchObject();
  }

  function get_chi_tiet_ban_tp($ma_phieu,$get_table = true) {
    $query = db_select('dt_vat_tu_ban_tp_chi_tiet', 'p');
    $query->fields('p', array('id', 'ten_vat_tu', 'don_vi', 'so_luong', 'don_gia', 'thoi_gian'));
    $query->condition('p.ma_phieu_ban_tp', $ma_phieu);
    $result = $query->orderBy('p.thoi_gian')->execute();
    $data = array();
    if ($get_table) {
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        $rs->ten_vat_tu,
        $rs->so_luong,
        $rs->don_vi,
        number_format($rs->don_gia),
        number_format($rs->so_luong * $rs->don_gia)
      );
    }
    }else {
      while ($rs = $result->fetchObject()) {
        $data[] = $rs;
      }
    }
    return $data;
  }

  function get_tong_tien_vat_tu() {
    $query = db_select('dt_vat_tu', 'vt');
    $query->addExpression('SUM(so_luong*gia_tien)', 'tong_tien');
    $query->condition(
      db_or()->
        condition('vt.ma_vat_tu', '%' . db_like('VT_') . '%', 'LIKE')
    );
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        array('data' => 'Tổng giá trị kho vật tư', 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($rs->tong_tien) . ' VNĐ', 'class' => array('text-bold')),
        //number_format($rs->tong_tien),
      );
    }
    return $data;
  }

  function get_vat_tu_theo_ten($ten_vat_tu) {
    $query = db_select('dt_vat_tu', 'vt');
    $query->fields('vt', array('id', 'ma_vat_tu', 'ten_vat_tu', 'hien_thi'));
    $query->condition('vt.ten_vat_tu', $ten_vat_tu, '=');
    $query->condition('vt.hien_thi', 0);
    $result = $query->execute();
    $data_khten = '';
    while ($rs = $result->fetchObject()) {
      $data_khten = $rs->ma_vat_tu;
    }
    return $data_khten;
  }
